Is there an alternative to max() for finding the last record?

[chandler]

I have a table which uses an incrementing non-autonumber counter field that gets concactinated with the year portion of now() to generate a job number in text format (01-9999).
At the new year, the counter will need to reset to 0000. However, in my query to get the last record, I'm currently using max(counter) to get the last record inserted. If I manually set the counter to 0 in the last record, the max(counter) obviously won't see that as the last record and at the new year, the jobnumber will start at max(counter)+1 (which is around 2500 right now) instead of 0+1 Chandler
I ran over my dogma with karma!

 

[tlhawkins]

hi Chandler

sounds like something that should have been thought of last year huh?

I do this to myself all the time.

It seems that at least with an Access DB the Max function will get it correct.
That is if I'm following you in thinking the progression would go like:

01-1
01-2
...
01-9999
02-1
02-2
...
02-9999
03-1
Where 01 - 03 is the year.

If that is the case then Max should continue to work.
I would recomend seperating those fields so that you can have a correct auto-number with no concatination and also have a field with the year, You could concatinate them for the Job # like this:

Select FieldYear + '-' + FieldKeyID as JobNumber
(hmm.. someone correct me if those +'s should be &'s )

It's up to you but I think that would cause less problems.