Reference number generator for access

Hi All,

I need to find a way of generating a Number to go in to a text box in access.
the first number has to be based on the date, the numbers after that can be anything.

i,e. "SMP1-1117"

The first number comes from the date, if it is April it is SMP1, May would be SMP2.
April = SMP1
May = SMP2
June = SMP3
July = SMP4
August = SMP5
September = SMP6
October = SMP7
November = SMP8
December = SMP9

The name of the text box it goes in to is "Text50" any help would be great guys.

Thanks so Much.
LVL 1
pnorris99Asked:
Who is Participating?
 
cquinnConnect With a Mentor Commented:

Private Sub Text50_Click()

Text50 = "SMP-" & cStr(Month(Calendar1) -3) & Format(Calendar1,"yy")) & "-"

End Sub
0
 
pnorris99Author Commented:
What I had come up with initially was,

Private Sub Text50_Click()

If Calendar1 >= ("01/11/2006") And Calendar1 < ("31/11/2006") Then

Text50 = "test"

End If

End Sub

I was going to use multiple IF statements for the different months.

I have also just thought would it be possible to have the script pick up the year and enter it in the Reference.
So it would be SMP1-06??     ??=Random number.

PS, I will update the point shortly, I am not stingy lol, I am just having bother with my card lol.

Thanks again guys.
0
 
pnorris99Author Commented:
I increased the points now to a more reasonable amount.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
cquinnCommented:
Private Sub Text50_Click()

If Calendar1 >= ("01/11/2006") And Calendar1 < ("31/11/2006") Then

Text50 = "SMP" & cStr(Month(Calendar1) -3)

End If

End Sub
0
 
cquinnCommented:
Opps - left some extraneous code in

Private Sub Text50_Click()

Text50 = "SMP" & cStr(Month(Calendar1) -3)

End Sub
0
 
pnorris99Author Commented:
Hi Mate thanks for that I have changed it a little to input the year also, do you know of a way displaying a 2 digit year i,e. YY rather than YYYY.

Also do you know of a way to create 2 extra digits on the end of this text that count up in 1's.

I am assuming this will be a little complicated as it is going to need to know what the last number was in order to add 1 to it.

SMP8-06-000  then the next record would be SMP8-06-001


Thanks again,

Private Sub Text50_Click()

Text50 = "SMP-" & cStr(Month(Calendar1) -3) & cStr(Year(Calendar1)) & "-"

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.