=Format(Now(),"yyddhnn")
Is not returning the 3 digit day. It is returning a two digit year, a two
digit day which is the current day of the month, not the current number of
the day in the year, a 1 digit hour and a two digit minute. The day appears
to be off by five just cooincidently, because it is the 24th. The h is
actually return 2 digits because the hour is past 9.
The correct formula is:
Format(Date(),"yy") & Format(DatePart("y",Date(),"000")) & Format(Time,"nn")
Which as I am writing this, is:
0723600
It was exactly 12:00 when I tested this.
Now, the incrementing may not be simple enough to understand at first
glance. I made a suggestion on a modification to John Spencer's repsonse,
but let me put it all together for you:
Private Sub GetNextNum()
Dim strReturn As String
strReturn = Format(Date(),"yy") & Format(DatePart("y",Date(),"000")) &
Format(Time,"nn")
Do While True
If Not IsNull(DLookup("[ReviewID]", "MyTable", "[ReviewID] = '" &
strReturn & "'") Then
strReturn = Left(strReturn,5) &
Format(Clng(Right(strReturn,2))+1,"00"))
Else
Me.strReviewID = strReturn
Exit Do
End IF
Loop
End Sub
Now, let's break it down:
'Format the YYJJJMM (Year, Julian Date, Minute) as a string
strReturn = Format(Date(),"yy") & Format(DatePart("y",Date(),"000")) &
Format(Time,"nn")
'Creates an endless loop True is always True (except when you watch CBS) <g>
Do While True
'Do a DLookup in the table for an existing duplicate value. If the value is
not in the table, Null will return. If you get a return, it is a duplicate
and you need to increment the number
If Not IsNull(DLookup("[ReviewID]", "MyTable", "[ReviewID] = '" &
strReturn & "'") Then
'Duplicate Number, increment
'Convert the last 2 characters of the string to a number
'Add 1 to the number
'Convert it back to a string
'Replace the last two digts of the current string with the incremented value
string
'Note this is different from my original post, but there is a bug in the other
strReturn = Left(strReturn,5) &
Format(Clng(Right(strReturn,2))+1,"00"))
Else
'We got a Null returned which means the value does not exist in the table
'Populate the control with the new value
Me.strReviewID = strReturn
'Jump out of the loop
Exit Do
End IF
Loop
If you need more help, post back
--
Dave Hargis, Microsoft Access MVP
Don said:
Steve, thanks for an easily understandable answer. There are only two
problems with the result that I get. First, it doesn't place the correct
julian date. My computer is set to the correct date, but the julian that is
being calculated is off by five days. Is there somewhere else in the system
that is driving this date?
Second, it is not accounting for possible duplicates and incrementing until
a non-duplicate is found. With the way you have it, there should not be a
problem as we would be lucky to add 5 new records per day, but in that
outside chance, what would you recommend? The other thread postings appear to
address the issue but I don't know where to place that code.
--
Thanks!
:
Assuming you are using the current date and time, set the Default Value
property of the field to:
=Format(Now(),"yyddhnn")
Steve
:
I have a control in an Access 2003 table that is set to text because of
alphanumeric needs. If I change the field to numeric, it breaks too many
other functions to be worth messing with.
Currently, this control [strReviewID] has a standard format that is use,
e.g. 2 digit century,3 digit julian date,2 digit minute. [Example, August 23,
2007 at 6:17 pm would equate to 0723517]. No duplicates allowed. Is it at all
possible to have this field automatically number itself when opening a new
record? Also, if for some odd reason the system does calculate a duplicate,
it should increment the number by one until a non duplicate is found.
