Full Version: auto generate number in access with julian date
UtterAccess Forums > Microsoft® Access > Access Date + Time
genautry
I am trying to auto generate an entry log id number that will include the julian date plus a three digit sequential number that will also reset back to 001 on the next day. Example: for today 11-08-2005 would be an entry log id number of 5321-001 or 5321001 either would be fine. Or even just using the standard date like 110805-001 or 110805001 would work. Any help would be great, Thanks
ScottGem
First there are several different definitions of julian date. Generally a Julian date is the number of days from some point. it could be from the end of the previous year as you seem to be indicating or from some other arbitrary point.
o do what you want would require 2 fields. One to hold the julian date and the other to hold the daily increment. The way I would do this is have a field for the julian date, call it JDate and another for the Increment. You would set the default value for JDate as:
=Val(Right(DatePart("yyyy",Date()),2) & DatePart("y",Date()))
The default for Increment would be:
=Nz(Dmax("[Increment]","tablename","[JDate] = " & Me!txtJdate) ,0) + 1
Now to display your number use:
=txtJadate & "-" & txtIncrement
genautry
Thanks for the help Scott
ScottGem
glad to assist
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.