Creating a unique number MMYY-001 MMYY-002

[Shane P via AccessMonster.com]

Hi there

I really need some help building a function that when I add a new record it
automatically creates a unique number in the format of MMYY-***

The table I have already contains a primary key which is an auto number but I
also need this number aswell (I have come into a company and they do not want
to get rid of the format. Currently they have an excel spreadsheet that they
update manually to get the next number)

Has anyone created a piece of code that can make a unique number in the
format above?

Any help on this matter I would be most grateful!!

Thank you

 

[Guest]

Hey Shane, I have a couple of questions:

-- Is the three digit number sequential? Is it numeric?

-- Can the three digit number be the same number? Are there stipulations?

-- How many records would be added in the given timeframe?

 

[Shane P via AccessMonster.com]

Hi Roachx

Thanks for your reply

Yes the numbers need to be numeric and and in sequence.

There can't be any numbers the same as they are booking numbers and there can
only be 1 number per booking.

Each month there are a maximum of 300-350 records per month so no need to
worry about needing 4 digits.

e.g this months first record would be 1105-001 then 1105-002 etc etc.

Then next month the 1st record would be 1205-001 1205-002 etc etc....


Thanks again

Hey Shane, I have a couple of questions:

-- Is the three digit number sequential? Is it numeric?

-- Can the three digit number be the same number? Are there stipulations?

-- How many records would be added in the given timeframe?

Hi there

[quoted text clipped - 12 lines]

Thank you

 

[Guest]

Shane, this function will get you on the right track. The function checks
table1 for the max value in the field [What]. This value is incremented by
one, formatted, and returned . You would also need to add some error
handling and another function to insert the data into the desired table :

Function fSetID() As String

Dim strYear As String
Dim strMonth As String
Dim strCode As String
Dim strMaxID As String
Dim strNewID As String

strYear = CStr(Format(Year(Date), "YY"))
strMonth = CStr(Format(Month(Date), "MM"))

strMaxID = DMax("[What]", "Table1", "[What] Is Not Null")

strCode = Format(Right(strMaxID, 3) + 1, "000")
strNewID = strMonth & strYear & "-" & strCode

Debug.Print strNewID

fSetID = strNewID

End Function

Hi Roachx

Thanks for your reply

Yes the numbers need to be numeric and and in sequence.

There can't be any numbers the same as they are booking numbers and there can
only be 1 number per booking.

Each month there are a maximum of 300-350 records per month so no need to
worry about needing 4 digits.

e.g this months first record would be 1105-001 then 1105-002 etc etc.

Then next month the 1st record would be 1205-001 1205-002 etc etc....


Thanks again

Hey Shane, I have a couple of questions:

-- Is the three digit number sequential? Is it numeric?

-- Can the three digit number be the same number? Are there stipulations?

-- How many records would be added in the given timeframe?

Hi there

[quoted text clipped - 12 lines]

Thank you

 

[Shane P via AccessMonster.com]

Can somone please help out with my problem.....

Thanks xRoachx for your example. When I run your function the debug result is
0105-003. I have then checked the table and the actual last number is 1105-
003.

Do you know why its given me a wrong result?

Shane, this function will get you on the right track. The function checks
table1 for the max value in the field [What]. This value is incremented by
one, formatted, and returned . You would also need to add some error
handling and another function to insert the data into the desired table :

Function fSetID() As String

Dim strYear As String
Dim strMonth As String
Dim strCode As String
Dim strMaxID As String
Dim strNewID As String

strYear = CStr(Format(Year(Date), "YY"))
strMonth = CStr(Format(Month(Date), "MM"))

strMaxID = DMax("[What]", "Table1", "[What] Is Not Null")

strCode = Format(Right(strMaxID, 3) + 1, "000")
strNewID = strMonth & strYear & "-" & strCode

Debug.Print strNewID

fSetID = strNewID

End Function

Hi Roachx

[quoted text clipped - 27 lines]

 

[David C. Holley]

Try adding +1 to the DMax() before you format it.

Can somone please help out with my problem.....

Thanks xRoachx for your example. When I run your function the debug result is
0105-003. I have then checked the table and the actual last number is 1105-
003.

Do you know why its given me a wrong result?

Shane, this function will get you on the right track. The function checks
table1 for the max value in the field [What]. This value is incremented by
one, formatted, and returned . You would also need to add some error
handling and another function to insert the data into the desired table :

Function fSetID() As String

Dim strYear As String
Dim strMonth As String
Dim strCode As String
Dim strMaxID As String
Dim strNewID As String

strYear = CStr(Format(Year(Date), "YY"))
strMonth = CStr(Format(Month(Date), "MM"))

strMaxID = DMax("[What]", "Table1", "[What] Is Not Null")

strCode = Format(Right(strMaxID, 3) + 1, "000")
strNewID = strMonth & strYear & "-" & strCode

Debug.Print strNewID

fSetID = strNewID

End Function

Hi Roachx

[quoted text clipped - 27 lines]

Thank you

 

[Shane P via AccessMonster.com]

Thanks David

So do you mean for me to do
strMaxID = DMax("[BookingNumber]", "tblBookingNumber", "[BookingNumber] Is
Not Null") + 1

I have tried that and I get a runtime error saying type mismatch.

Try adding +1 to the DMax() before you format it.

Can somone please help out with my problem.....

[quoted text clipped - 36 lines]

 

[David C. Holley]

Sounds like you need to extract the last characters and convert them to
integers

strMaxId = [original statement]
'Grab the last three characters and increment them
nextNumber = CInt(Right(strMaxId, 3)) + 1
'build the new number
newId = Left([original statement], Len([original statement])-3) & nextNumber

The last statement strips away the last three characters and replaces
them with the value from the prior statement.

Keep in mind that this is all off the top of my head. Also, it assumes
that you'll only sequences up to 999

Thanks David

So do you mean for me to do
strMaxID = DMax("[BookingNumber]", "tblBookingNumber", "[BookingNumber] Is
Not Null") + 1

I have tried that and I get a runtime error saying type mismatch.

Try adding +1 to the DMax() before you format it.

Can somone please help out with my problem.....

[quoted text clipped - 36 lines]

Thank you