Serial Number that Reset by date

B

Bassel

Hello
I want to have a number that starts at 01 , and auto increases ( max will
end at 15).
I used ID=DMax("ID","Vendors")+1 in a form. This is working fine.
However ,I want to have this number (ID) reseted when the date change.
So every day it starts again fom 01.

Can you help me in this problem
 
K

KARL DEWEY

Add a date field. Use an Iif statement comparing last record date with
Date() and increment if same day or start new if different.
 
J

John Vinson

Hello
I want to have a number that starts at 01 , and auto increases ( max will
end at 15).
I used ID=DMax("ID","Vendors")+1 in a form. This is working fine.
However ,I want to have this number (ID) reseted when the date change.
So every day it starts again fom 01.

Can you help me in this problem
Click to expand...

Use

ID = NZ(DMax("[ID]","[Vendors]","[Datefield] = #" & Date() & "#")) + 1

This will look up the maximum ID for today's date (if there is one)
and increment it by one; if this is the first record for the day, the
DLookUp will return a NULL result, which NZ will convert to a zero so
the first record for the day gets an ID of 1.

John W. Vinson[MVP]
 
Top