Create auto number base on MM DD and number increment

[jimfrmla]

Does anyone know how to create and autonumber field where. You proceed the the number by the current date and then you compare to the last number accepted to generate the next number. Example

MMDD-nnnn (format)
0721-0034
0721-0035
0723-0036 (next day)
0723-0037
0723-0038
0724-0039 (nex day)

 

[PHV]

Take a look here:

http://www.tek-tips.com/faqs.cfm?fid=184

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sleonard1432]

Here is a simple solution. Not too complicated. I have listed some sample data to use to test my approach to this automated process. You can twist this up into your own variation to better suit your needs. However, this will give you a baseline to start with.

I added two data fields Fname and Lname to show you how to add the first initial of both the first and last name to the unique identifier, this is done by using Left&([txtFname],1 & [txtLname],1). So your sample will look like this

DD/MM/YYYY-anum-fname-lname
01/01/2005-001SL


SAMPLE DATA
****Table(s)****
Table Name = tblData
FIELD NAME DATA TYPE
pkeyId AutoNumber
datDate Date/Time
strUniqueId Text
strFname Text
strLname Text

****Form(s)****
Form Name = tblData
Form Control Source = tblData
CONTROL TYPE NAME CONTROL SOURCE
TEXT BOX txtId pkeyId
TEXT BOX txtDate datDate
TEXT BOX txtUniqueId strUniqueId
TEXT BOX txtFname strFname
TEXT BOX txtLname strLname
LABEL lbl2 VIEW ASSOCIATED VB CODE
BUTTON cmdAdd VIEW ASSOCIATED VB CODE

LABEL (lbl2) VB CODE
Private Sub lbl2_Click()
txtFname.SetFocus
lbl1.Visible = False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


BUTTON (cmdAdd) VB CODE
Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

DoCmd.GoToRecord , , acNewRec

lbl1.Visible = True

txtDate.Value = Date

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

I hope this helps kick start what you are looking to achieve. There are other ways to produce the same results, however they are much more complicated.

Good luck!

 

[BSman]

Since your ID field is a text field (because of the dash) and also because you want the leading zeroes in the year, you can create the next number by adding 1 to the highest (incremented) number part of the ID field and then appending to the year and month plus a dash. Here's an example of the code to handle the incremented number part of the ID:

(ID as string, let's say based on your example that it is 9 characters, the last 4 are the number (which will never go over 4 digits))

Dim lngNew as Long
Dim strNextID as string

' get the last 4 characters of the ID, convert to a number
' and increment by 1

lngNew = val(mid$(ID,6,4)) + 1

' add 10000 to lngnew, convert it to a string, extract just the last 4 characters, and concatenate the result with the date. (Here I'm just using the date part of the previous ID. You can just extract the text of the year and month of the date from the current date.)

strNextID = left$(ID,5) & right$(Str$(10000 + lngNew))

Actually you can combine these into one statement (and it could also include the current year and month in the statement). I've shown them as separate statements for clarity.