Creating a unique tailor made ID Number

[mike1975]

I have a table where calls are logged - it has a Unique ID no which is set as the Primary key - I would like this or another field to be of a certain format made up of the Month number and the ID - so for example a call in January would be 010001 or in December 120010 etc etc id there anyway of doing this?

Mike

 

[jflachman]

It sounds like you know how to get your unique ID, so I won't go into that part. Assume the field that contains your unique ID is Me![IDField] and the field you want to have your total ID is Me![FullID]

You can do this using code:

I'll assume that you want to have your ID be a string so that you can maintain your leading zero.
[tt]
dim sUniqueID as String
dim iMonth as Integer

iMonth = Val(Format(Me![DateField],"m&quot)
If (iMonth<10) Then
sUniqueID = &quot;0&quot; + str(iMonth)
Else
sUniqueID = str(iMonth)
End If

Me![FullID]=sUniqueID
[/tt]
Hope that helps.


Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[jflachman]

BTW, if you don't have a way to get your unique ID portion of the number, there is a FAQ from MichaelRed in this forum that may help.

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[mike1975]

Jonathon,

Thanks for the help - Sorry I am only a novice user of Access - I have just chosen Autonumber in the Design of the table so set it as a Unique ID - where do I build this code in relation to the table?