Autonumber works great, but my boss needs the ID to be a number that begins with the 2 digit year. For example 08-465 and 08-466.
I know this must be a common requirement, so what is the best way to handle it? Should I simply create a new text field ([newID] and then use VB in a newrecord event that:
Me.newID = DatePart("YY", Now) & "-" & Me.ID
This works, but I want to see if anyone thinks there is a better way?
Peter
4 3513
Autonumber works great, but my boss needs the ID to be a number that begins with the 2 digit year. For example 08-465 and 08-466.
This works, but I want to see if anyone thinks there is a better way?
Peter
Format your AutoNumber field in your table as such:
Format: "08-"000
Bender
I guess that would work, as long as I code in some way to change that default setting every Jan 1, to match the new year.
Thanks!
Peter
I would store the year in the record itself, as a numeric field whose default value is '=year(date())'. You can combine this with the value of the autonumber field to create the record ID which you need (in format YY-NNNN).
If you just format the autonumber value without explicitly storing the year you will not be able to answer a query such as 'list all records for year 08 (or 09 etc) which are over X in value', say.
I use similar techniques when dealing with purchase orders for differing financial years (the year number is part of the record).
Although an autonumber is unique, the customer requirement to have the year as well indicates a compound key (year+reference).
Does your customer require that the ID's restart each year (e.g. from 1 Jan 09 reference 09-0001? If so, the autonumber solution has a difficulty, as autonumbers are not directly resettable to a start value. There are other ways to cope with this using a Long field and incrementing the value programmatically.
Hope this helps.
Regards
Stewart
Stewart, that's a great idea, and I already had a date field to each record, so I already had the information needed to create the YYYY-0000 format for my ID numbers.
But, yes, I do have to reset the numbers each year back to 1. I had completely forgot about this issue! So what is the best way to use an incrementing Long Field as you stated.
Do I..
1: create a new table with an ID field - Table!NewID.ID
2. Begin with a "1" in the ID field each Jan 1st.
3. Pull this table's field every time I add a new record and use it for the unique ID number for my records on my main form
4. Increment the number in Table!NewID.ID when I update my record.
Is that the standard way around this issue?
Peter
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John Baker |
last post by:
Hi:
I know this is a strange question, but I have inherited a system where files are copied
and records re auto numbered (as an index field) )frequently, and I am wondering how high
the number...
|
by: Apple |
last post by:
1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.
2. I had create a query by making relation to a table & query,...
|
by: allyn44 |
last post by:
HI--what I am trying to do is 2 things:
1. Open a form in either data entry mode or edit mode depending on
what task the user is performing
2. Cancel events tied to fields on the form if I am in...
|
by: junkaccount |
last post by:
Hello,
I currently have a field named QuoteNumber in a table named Quotes.
The field is set as autonumber and is used to assign sequential numbers
as users enter information in the table through...
|
by: Zellan |
last post by:
Is there a way to add a prefix to an autonumber field?
Zellan
|
by: feeman |
last post by:
I have a table stores records for moving stock around to different
sites, we use a unique number for this paperwork, it stats with 2MER
and then it has sequential numbering after this. Is there a...
|
by: bobh |
last post by:
Hi All,
I can't remember what it is called but I know it did have a name I
thought I remembered it as a 'cascading event' but I don't think
that's what it really is called.
senario:
your...
|
by: klarae99 |
last post by:
Hello,
I am working in Access 2003 to create a database to record information about an annual fundraiser. I was hoping someone could review my table structure and make sure that it is normalized...
|
by: beadman |
last post by:
Hello
I am trying to use autonumber on an invoice with the prexif "CRW", so the Invoice ID will be CRW00001, CRW00002..and so on. I have put a Format of "CRW"00000 in the table design for the...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |