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" <priya.nrj@gmail.com> wrote in message
news:1119457070.980671.212000@g49g2000cwa.googlegr oups.com...[color=blue]
> 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
>[/color]
