Restart Number Field on Month change

[TOWBRI]

I have a database which requires a reference field containing a concatenated number (from a number field) and the month/year (from a date field) the end result being = 001/04/02. So far I have managed to get the number field to automatically advance by using the DMax function, however I now need to get the number field to reset back to 1 when we enter a new month, so when we get to May it would be 001/05/02. Can any body help? Cheers.

 

[Geoff Codd]

you probably want something like this, i havn't tested it

if isnull(DMax("[Ref]", "TableName", Mid([Ref],5,2) = Format(Date(),"mm") then
Me!Ref = "001/" & Format(Date(),"mm/yy")
Else Me!Ref = Format(Clng(Right(DMax("[Ref]", "TableName", Mid([Ref],5,2) = Format(Date(),"mm"),1,3))+1,"000") & "/" & Format(Date(),"mm/yy")

[This message has been edited by Geoff Codd (edited 04-29-2002).]