Create a unique ID using dates?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a unique record ID using the date (mmddyy) and an
incremental number. Ideally I would like the ID to appear as 102305001 where
the last three digits go up by one for each record. Is there an easy way to
do this through VBA?
 
Try this, function that get the date and return the new count

Function GetNewNumber(DateInFormatMMDDYY as String)
Dim MyDb as DAO.DataBase, MyRec as Recordset
Set MyDb = currentDb
Set MyRec = MyDb.OpenRecordSet("SELECT Val(Right([FieldName],3)) AS
DateCount FROM TableName WHERE Left([FieldName],6) ='" & DateInFormatMMDDYY
& "'")
If MyRec.Eof then
GetNewNumber= DateInFormatMMDDYY & "001"
Else
GetNewNumber = DateInFormatMMDDYY & format(MyRec!DateCount + 1,"000")
End If
End Function
 
Erik Lindquist said:
I would like to create a unique record ID using the date (mmddyy) and an
incremental number. Ideally I would like the ID to appear as 102305001 where
the last three digits go up by one for each record. Is there an easy way to
do this through VBA?
Click to expand...

Here's a multi-purpose function that with a few small changes you can use to
plug in anywhere:

Function DateNum() As String
'********************************************************************
' Name: DateNum
' Purpose: Generate an incremental "number" based on the year
'
' Author: Arvin Meyer
' Date: July 27, 2003
' Comment: Assumes Table1 As Table and CaseNum As Field
' Generates in the format of 20030001, 20030002, etc.
' Seed the first number if other than 0000
'********************************************************************
On Error GoTo Error_Handler

Dim intNumber As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select [CaseNum] from [Table1] order by
[CaseNum];")

If Not rs.EOF Then
rs.MoveLast
If Left(rs.Fields("CaseNum"), 4) = CStr(Year(Date)) Then
intNumber = Val(Mid(rs.Fields("CaseNum"), 5)) + 1
Else
intNumber = 1
End If
End If

DateNum = Year(Date) & Format(intNumber, "0000")

With rs
.AddNew
!CaseNum = DateNum
.Update
End With

Exit_Here:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

Error_Handler: 'If someone is editing this record trap the error
Dim intRetry As Integer
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Err.Number, vbOKOnly, "Another user editing this number"
Resume Exit_Here
End If
Else 'Handle other errors
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Problem
Generating Number"
Resume Exit_Here
End If

End Function

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Sorry, but you need to add max to the recordset
Set MyRec = MyDb.OpenRecordSet("SELECT Max(Val(Right([FieldName],3))) AS
DateCount FROM TableName WHERE Left([FieldName],6) ='" & DateInFormatMMDDYY
& "'")

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Ofer said:
Try this, function that get the date and return the new count

Function GetNewNumber(DateInFormatMMDDYY as String)
Dim MyDb as DAO.DataBase, MyRec as Recordset
Set MyDb = currentDb
Set MyRec = MyDb.OpenRecordSet("SELECT Val(Right([FieldName],3)) AS
DateCount FROM TableName WHERE Left([FieldName],6) ='" & DateInFormatMMDDYY
& "'")
If MyRec.Eof then
GetNewNumber= DateInFormatMMDDYY & "001"
Else
GetNewNumber = DateInFormatMMDDYY & format(MyRec!DateCount + 1,"000")
End If
End Function
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



Erik Lindquist said:
I would like to create a unique record ID using the date (mmddyy) and an
incremental number. Ideally I would like the ID to appear as 102305001 where
the last three digits go up by one for each record. Is there an easy way to
do this through VBA?
Click to expand...
Click to expand...
 
Erik said:
I would like to create a unique record ID using the date (mmddyy) and
an incremental number. Ideally I would like the ID to appear as
102305001 where the last three digits go up by one for each record.
Is there an easy way to do this through VBA?
Click to expand...

Yes. *Store* the data in two fields [CreatedOn] and [ID] (for example).

There are numerous cases where needing to know when a record was created is
useful so I nearly always have such a field anyway. You can easily use an
expression to *display* a combined ID to your users even though you use separate
fields for storage. Having them separate makes the calculation for determineing
the next ID for new records easier and more efficient. I use the form's
BeforeUpdate event.

If IsNull(Me![ID]) Then
Me![ID] = Nz(DMax("[ID]", "TableName", "Year(CreatedOn) = Year(Date())"), 0)
+ 1
End If

At the table level [CreatedOn] would have a default value of Now() and [ID]
would have no default value.

The above translates to "find the highest existing ID created in the current
year and add one to it". The Nz() is needed for the very first record of the
year. For display on your forms and reports use the expression...

=Format([CreatedOn], "mmddyy") & Format([ID], "000")

Actually after typing the above I realize that I am assuming you want the number
to start over each year even though you didn't actually specify that in your
post. If you don't then you can just leave out the third argument of the DMax()
function.
 
Back
Top