Assigning Unique Number

[Erin]

I need to create a unique ID number based on the date the record was
initiated. The ID number needs to begin at "1" with the start of each year
and then increase by "1" for each record added for that year. How do I do
this? My code right now:

Private Sub Status_AfterUpdate()
If IsNull(Me.[ID]) Then
Me.[ID] = Nz(DMax("[ID]", "tbl_FQInfo", "Year(InitiationDate) = " &
Year(InitiationDate))) + 1
End If
End Sub

Thanks!

 

[Jeff Boyce]

Erin

If you want to have a year and a sequence number, why not use a Date/Time
field and a Number field? You can always concatenate the two together for
display purposes, but it's not a very good idea to stuff more than one fact
into one field.

You provided a code example ... so why not use that? What happens when you
use that?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TimH]

I needed to set up a unique Id number from various parts a couple of months
ago and with help from the group this was the result and it worked; you can
select any potion, you see there is the date reference as well:

Private Sub PtKey_GotFocus()
Dim strTmp As String
Dim dtVisitDate As Date
Dim strPtKey As String
Dim strLastName As String

strTmp = Me.Visit_Date & ""

If IsDate(strTmp) Then ' from John Spencer
dtVisitDate = DateValue(strTmp) ' from J.S.
strTmp = Format(dtVisitDate, "mmddyyyy") 'yyyymmdd--From J.S.
End If

'Build_VisitDate = strTmp
strTmp = strTmp & "-"
strSSN = Me.SSN & ""
' OK so it works, I used the code below to take a full SSN
' and convert it to the last 4
strSSN = Right(strSSN, 4)
strTmp = strTmp & strSSN
strLastName = Me.LastName & ""
strLastName = Left(strLastName, 1)
strTmp = strTmp & strLastName
PtKey = strTmp

End Sub

Good luck

 

[Erin]

Right now that code isn't assigning a new number based on a different year -
it's assigning sequentially but not based off the year and I can't figure out
why.

 

[Erin]

To be more clear:

For 2005 I want to have record ID number 1, 2, 3, 4 etc.
For 2006 I also want to have record ID numbers 1, 2, 3, 4 etc.

This ID number is then combined with the year created (-05 or -06), the
document type precursor (for example FQ) and then the unique ID number -001,
-002.

So in my form field for the document number it concatenates the ID number
that was assigned above based on the code (which isn't working correctly) to
get:

FQ05-001
FQ05-002
FQ06-001
FQ06-002
and so on for each year.

Thanks!