asked on

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.

pnorris99

ASKER

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.

pnorris99

ASKER

I increased the points now to a more reasonable amount.

cquinn

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

cquinn

Opps - left some extraneous code in

Private Sub Text50_Click()

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

End Sub

pnorris99

ASKER

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

ASKER CERTIFIED SOLUTION

cquinn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial