Finding Max # in combined # & Alpha field 1

[BabyPowder2u]

I am recording a sequence number in the format of YYAA#### where YY is the last 2 digits of the year, AA is 2 alpha characters (which will always be the same) and #### is the sequence number.

I need to be able to do a lookup (or query) that will return the last sequence number for a given year. So if it is Dec 05 and the record is applicable to year 2005 it would give me the max value 05AA####, if it is applicable to year 2006 I would get the max value 06AA####.

Any help with this would be appreciated.

T

 

[PHV]

DMax("Right([the field]", 4), "[the table]", "Left([the field], 2)='" & Format(Date, "yy") & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BabyPowder2u]

PHV,

Thanks for getting back with me so quick. I will try the code out in a bit.

T

 

[PHV]

OOps, some typo:
DMax("Right([the field], 4)", "[the table]", "Left([the field], 2)='" & Format(Date, "yy") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BabyPowder2u]

Thanks PHV,

One other question related to this. This assumes the count field will be 4 digits. How can I create this field so the first x # of digits pad with 0's? i.e. i want the count to start with 0001.

 

[PHV]

Have a look at the Format function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886