auto number

[michellerobinso]

are you able to do something with the auto number.

i'd like it to display R/N "month" auto number "year".

i cant seem to get it to display the month and year

 

[Matt Greatorex]

When you say you want it to display R/N "month" auto number "year"., what do you mean by 'it'?

If you want a text field on a form to display that, then you can use ][tablename]![name of the autonumber column][/ to get the autonumber value and format the date to get the month and year.

 

[michellerobinso]

what i mean is that normally when you enter a new record, the unique id creates a new number.

what i want is the auto number to display the record number, primary key, what ever you call it but i want it formated that it displays in the box the auto number with R/N at the beginning, which i have done, but i also want it to display the month like 01 02 03 04 for jan feb mar apr. is this possible?

 

[Matt Greatorex]

As far as displaying the number with RN before it is concerned, you can do this easily in VBA (just construct a string made up of "RN" & autonumber.

With the date part, I don't know of a way to do it, other than storing the date the record gets created in a separate field (again, easy to do using VBA).

Maybe one of the more expert bods out there in the forum has another way of doing it?

 

[neileg]

You should not use an autonumber for this. There is no way to guarantee that autonumbers are contiguous (gap free) only that they are unique. You should create a incrementing number using DMax(). This has been covered often enough before.

You can construct your compound number using a calculation. I assume you hold the relevant date in a field called [MyDate] and the sequential number as [SeqNo].

="R/N"&DatePart("m",[MyDate])&[SeqNo]&DatePart("yyyy",[MyDate])

I would use an unconnected autonumber as the actual Primary Key.

 

[michellerobinso]

thank you very much for the information. i was wondering if you could break that down for me a bit more.

im not very experienced with MS Access, so some of this has gone straight over my head.