Generating a sequential number based on the date entered

[JesseD]

I am looking to create an access 2010 data base where each entry is generated from a user completed form. Once the form is completed I need to create a unique, sequential number based on the date of entry.



For example: The first entry submitted between 6/1/2011-5/31/2012 would need to be "01-001". The second submitted in this time frame would be "01-002"
The first entry submitted between 6/1/2012-5/31/2013 would be "02-001", and so on.

Any help/suggestions would be greatly appreciated.

[orange]

Just saw your post and my initial thought is Why do you need this particular format?
Just curious.

[rpeare]

I'm with orange this is an odd request.

Are you storing your dates as dates (for instance a starting date and closing date) or is it a text string? how do you know all of your data entry is going to be accurate if it's not two date fields? because 01/01/2011 would not equal 1/1/2011, etc.

[JesseD]

We number our orders based on the fiscal year of which they were received.

[rpeare]

Ok so you're looking at the invoice creation date

so your FY begins on 6/1 and ends on 5/31 so fy11 would go from 6/1/2010 through 5/31/2011?

If so you could (on your data entry form) populate the field you're interested in with something like

Dim fy As Integer
Dim RecCt As Long

If DatePart("m", [PODATE]) >= 6 Then
    fy = Right(DatePart("yyyy", [PODATE]), 2) + 1
Else
    fy = Right(DatePart("yyyy", [PODATE]), 2)
End If

RecCt = DCount("[POID]", "Tbl_POs", "[PODATE] between #" & CDate("6/1/" & fy - 1) & "# AND #" & CDate("5/31/" & fy) & "#")

If RecCt = 0 Then
    poid = fy & "-001"
Else
    poid = fy & "-" & Right("00" & RecCt + 1, 2)
End If

You'd have to substitute in your table name and field name but this works for me.