Custom function Second()

[Killer42]

Thanks for that, PEB. So MrWm, does that solve your problem?

[MrWm]

Well, it does remove the code error I was having, however the query that I am using (based on PEB's Sept 21st post) displays an error in the ranking field of every record:

Query -
Ranking:IIF(Sho w_last_criteria ([Your_Field])=[Your Field with Criteria],Set_last(nz(Sh ow_last([Your Field with Criteria]))+1, [Your Field with Criteria]),Set_last(1, [Your Field with Criteria]))

My query looks at EmpID, Date, Score, and the Ranking. I am trying to rank the scores for each user for each month, to create the crosstab query. I have used EmpID to replace [Your_field] and [Your Field with Criteria].

I can't seem to find my error...any suggestions?

Also, in the query above there is a function nz() that I don't know what it does. Is this the problem?

[Killer42]

Hm... I suppose the nz() function might be the problem. But it's a function built into MS Access, so as far as I know it should be alright.

One thing I would suggest is to use MS Access directly to test your query. It's much quicker and easier to play with the query in Access's GUI query designer. Once you've got it working, then you just go to the SQL view and copy it. (Note, you can also paste you SQL into there in the first place.)


Quoted from online help in Access...

Nz Function
You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.