generated ID

  • Thread starter Thread starter jervin via AccessMonster.com
  • Start date Start date
J

jervin via AccessMonster.com

Hi

I need the auto generated ID configure that exactly look like this:

52490001

where:
5 = the current year
249 = the julian calendar date equivalent (can this be automatic? or is there
a way a program to check the current julian date?)
0001 = the 4 digits series no..( this will then reset and start to 0001 again
each time the julian date change)

thank you for your help again
 
jervin via AccessMonster.com said:
Hi

I need the auto generated ID configure that exactly look like this:

52490001

where:
5 = the current year
249 = the julian calendar date equivalent (can this be automatic? or
is there a way a program to check the current julian date?)
0001 = the 4 digits series no..( this will then reset and start to
0001 again each time the julian date change)
Click to expand...

Is the ID field a Long Integer, or is it Text? I'll guess that it's a
number. And do you want the day number to always be three digits? I'll
guess so. Then your function might look like this:

'----- start of code -----
Function fncNextID() As Long

Dim strYear As String
Dim strDay As String
Dim strBase As String
Dim varLastID As Variant
Dim lngNextID As Long

strYear = Right(Format(Date, "yyyy"), 1)
strDay = Right("00" & Format(Date, "y"), 3)
strBase = strYear & strDay

varLastID = DMax("ID", "YourTableName", "(ID \ 10000) = " & strBase)

If IsNull(varLastID) Then
lngNextID = CLng(strBase & "0001")
Else
lngNextID = varLastID + 1
If lngNextID > CLng(strBase & "9999") Then
Err.Raise vbObjectError + 1, , _
"Pool of ID numbers for this year and day has been
exceeded")
End If
End If

fncNextID = lngNextID

End Function
'----- end of code -----

That's all air code, but it should be close to correct, based on the
assumptions I made.
 
thank you sir...

Dirk said:
Hi
Click to expand...
[quoted text clipped - 8 lines]
0001 = the 4 digits series no..( this will then reset and start to
0001 again each time the julian date change)
Click to expand...

Is the ID field a Long Integer, or is it Text? I'll guess that it's a
number. And do you want the day number to always be three digits? I'll
guess so. Then your function might look like this:

'----- start of code -----
Function fncNextID() As Long

Dim strYear As String
Dim strDay As String
Dim strBase As String
Dim varLastID As Variant
Dim lngNextID As Long

strYear = Right(Format(Date, "yyyy"), 1)
strDay = Right("00" & Format(Date, "y"), 3)
strBase = strYear & strDay

varLastID = DMax("ID", "YourTableName", "(ID \ 10000) = " & strBase)

If IsNull(varLastID) Then
lngNextID = CLng(strBase & "0001")
Else
lngNextID = varLastID + 1
If lngNextID > CLng(strBase & "9999") Then
Err.Raise vbObjectError + 1, , _
"Pool of ID numbers for this year and day has been
exceeded")
End If
End If

fncNextID = lngNextID

End Function
'----- end of code -----

That's all air code, but it should be close to correct, based on the
assumptions I made.
Click to expand...
 
Back
Top