generate random number

[Guest]

I require a function where Access will generate an Automatic identification
number beginning with the letters 'BNS'; Also after the letters ‘BNS’ I
would like the date to be displayed in number form. Finally after the BNS and
date I would like a / or . and then two numbers

For example if it were today’s date (2nd August 2006):

BNS02082006.00 or BNS02082006/00

BNS02/08/2006.00 or BNS02/08/2006/00

(Any of the above combination would be fine)

Therefore, the date would stay the same and the two last would change every
time a new record is entered. E.g. 01, 02, 03, 04, 05 etc

Also the numbers have to be unique. I am still a novice in Access so if
someone can give me a step by step guide it would be great!

 

[Joseph Meehan]

I require a function where Access will generate an Automatic
identification number beginning with the letters 'BNS'; Also after
the letters 'BNS' I would like the date to be displayed in number
form. Finally after the BNS and date I would like a / or . and then
two numbers

For example if it were today's date (2nd August 2006):

BNS02082006.00 or BNS02082006/00

BNS02/08/2006.00 or BNS02/08/2006/00

(Any of the above combination would be fine)

Therefore, the date would stay the same and the two last would change
every time a new record is entered. E.g. 01, 02, 03, 04, 05 etc

Also the numbers have to be unique. I am still a novice in Access so
if someone can give me a step by step guide it would be great!

You are talking about two fields. The first is the date the second is a
incremented number 01 through 99. The BNS would not be stored and would
added anytime you wanted to display the number. When you want to display
or print the identification number you would combine the text BSN and the
two fields.

I suggest that you consider at least two things. First are the letters.
From experience we all know that when the boss tells you that he always
wants BNS, two weeks after you have everything working, there will be a
merger and now it will be BNS&SBE. Maybe they will demand that all the
future records use the new prefix or all including existing records or them
may change there mind a few times along the way. Also I can almost
guarantee that within a few months you will end up with 105 new records in
one day.

Consider those issues now not later.

I am going to let someone who has played with the incrementing number
(01 - 99) thing to make a suggestion about the best way to handle that.