By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,650 Members | 1,778 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,650 IT Pros & Developers. It's quick & easy.

Problem UNIQUE KEY generation !

P: n/a
hi..
Iam stuck with soemthing which might be very simple. but.....any
suggestions would be great to help this newbiee...:(
I want to generate a key for my table in the format of - yyyy-mm-6 dig
number ! Actually I have to store tapes and they have to have an ID for
them which are implicative of the year and the month they were created
!!
My ques is , HOW do I create 3 fields in the DB ---one for the year,
one for month and one for 6 digit number and display them together as a
SINGLE ID in the FORMS ?? But...i have no clue as to how i can do that
too. I also want this last 6 digit number to be incremented every time
a new record is added !!
For eg - 20005-06-000001
2005-06-0000002

The year and month should also be obtained using the Year() and month()
functions !

Anyhelp would be appreciated.
thanks,
Priya

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
To create and display a YEAR-MO-NNNNNN identifier,
create a YearID field, type Text, length 4; a MonthID field, type Text,
length 2; and an NumberID field. You can search newsgroup archives and find
code for generating a monotonically increasing number. You can let it run,
or start it over for each new month-year combination.

The Default for YearID can be: Format(Year(Date),"0000") and the Default for
MonthID can be: Format(Month(Date),"00").

I don't suggest AutoNumber for the NumberID field because there is no
guarantee that it will increase by one for each record, and thus Autonumbers
should not be used for display purposes -- the only guarantee uniqueness,
and are for internal use in joining and identifying.

Concatenate the fields to display them in a single text box -- you can
create a calculated field in the Query to which you bind the record data:
YearID&"-"&MonthID&"-"&Format(NumberID,"000000"). Alternatively, you can use
three text boxes and display each in its own text box.

Larry Linson
Microsoft Access MVP
"priya" <pr*******@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
hi..
Iam stuck with soemthing which might be very simple. but.....any
suggestions would be great to help this newbiee...:(
I want to generate a key for my table in the format of - yyyy-mm-6 dig
number ! Actually I have to store tapes and they have to have an ID for
them which are implicative of the year and the month they were created
!!
My ques is , HOW do I create 3 fields in the DB ---one for the year,
one for month and one for 6 digit number and display them together as a
SINGLE ID in the FORMS ?? But...i have no clue as to how i can do that
too. I also want this last 6 digit number to be incremented every time
a new record is added !!
For eg - 20005-06-000001
2005-06-0000002

The year and month should also be obtained using the Year() and month()
functions !

Anyhelp would be appreciated.
thanks,
Priya

Nov 13 '05 #2

P: n/a
Pssst.... Use DMAX. Search the NG. Or www.mvps.org That question has
been asked many times, and the answers are there for the searching.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.