469,306 Members | 2,504 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

generating auto number with a specific prefix

hello,

this seems to be a hard question so far and noone has been able to help
with this. is it possible to have access start an autonumber with a
prefix according to the year when the data is entered. for example, if
i entered something in 2004, i would like the number to bigin with
2004003, 2004004, 2004005... and same for 2005001, 2005002...?
much and great appreciation for suggestions.

thank you,

the hotshot

Nov 13 '05 #1
4 10214
If you search this group on "autonumber" you'll find a lot of advice.

Autonumber is a field type that is really a long integer, with some hidden
code
to increment the value for every added record. I do NOT recommend
any of these pseudoID forms that use combinations of initials/date/other,
as they ALL will eventually fail and most are vulnerable to user input
mistakes.
Trying to "imbed" data within an ID is almost always a bad idea.
Most good data designs have a primary key for each table,
generally an autonumber field. Most applications do not display that ID, but
use it internally for relational integrity. If you want to show the records
entered in any particular year,
you should be using a query to filter records based on a real date range.

That said, one of the more common solutions for what you ask, is to
concatenate
2 fields in the format that you require. Keep the standard autonumber
field, then add a [DateEntered] field
to your table and data entry form. Make this a date/time field type and set
the default value to Date().
This will automatically enter the system date anytime a new record is added.
On your forms/reports
that need the pseudoID displayed, create an unbound textbox and set its
control source to something like:
=DatePart("yyyy",[DateEntered])&[AutonumberFieldName]

-Ed


"the hotshot" <ho*****@inbox.lv> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
hello,

this seems to be a hard question so far and noone has been able to help
with this. is it possible to have access start an autonumber with a
prefix according to the year when the data is entered. for example, if
i entered something in 2004, i would like the number to bigin with
2004003, 2004004, 2004005... and same for 2005001, 2005002...?
much and great appreciation for suggestions.

thank you,

the hotshot

Nov 13 '05 #2
thank you for the great advice Ed. i cant pass the stage of creating an
unbound textbox. maybe it would be possible to also help with this
step? i tried looking on the net on how to do it, but there are no
clear explanations, only definitions of it.

thanks again,

the hotshot
Ed Robichaud wrote:
If you search this group on "autonumber" you'll find a lot of advice.

Autonumber is a field type that is really a long integer, with some hidden code
to increment the value for every added record. I do NOT recommend
any of these pseudoID forms that use combinations of initials/date/other, as they ALL will eventually fail and most are vulnerable to user input mistakes.
Trying to "imbed" data within an ID is almost always a bad idea.
Most good data designs have a primary key for each table,
generally an autonumber field. Most applications do not display that ID, but use it internally for relational integrity. If you want to show the records entered in any particular year,
you should be using a query to filter records based on a real date range.
That said, one of the more common solutions for what you ask, is to
concatenate
2 fields in the format that you require. Keep the standard autonumber field, then add a [DateEntered] field
to your table and data entry form. Make this a date/time field type and set the default value to Date().
This will automatically enter the system date anytime a new record is added. On your forms/reports
that need the pseudoID displayed, create an unbound textbox and set its control source to something like:
=DatePart("yyyy",[DateEntered])&[AutonumberFieldName]

-Ed


"the hotshot" <ho*****@inbox.lv> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
hello,

this seems to be a hard question so far and noone has been able to help with this. is it possible to have access start an autonumber with a
prefix according to the year when the data is entered. for example, if i entered something in 2004, i would like the number to bigin with
2004003, 2004004, 2004005... and same for 2005001, 2005002...?
much and great appreciation for suggestions.

thank you,

the hotshot


Nov 13 '05 #3
Sure. Unbound text boxes are just a text box control on a form/report that
are not "bound" to a specific field in a data table/query. To create one,
do not drag and drop from the field list. Use the toolbox wizard to place a
text box on your form. In the control source property, put the expression:
=DatePart("yyyy",[DateEntered])&"-"&[AutonumberFieldName]

(assuming that you have bound controls on your form, named
"DateEntered" -bound to a date/time field in your table/query that has a
default value of Date() - and "AutonumberFieldName" bound to the primary
key of your table/query - more likely called something like CustomerID)

This will then display (but not store in a table) the combined values -i.e.
"2004-001", "20043-002", etc. You would need to copy that control to any
form/report that needs to display the same info.
-Ed

"the hotshot" <ho*****@inbox.lv> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
thank you for the great advice Ed. i cant pass the stage of creating an
unbound textbox. maybe it would be possible to also help with this
step? i tried looking on the net on how to do it, but there are no
clear explanations, only definitions of it.

thanks again,

the hotshot
Ed Robichaud wrote:
If you search this group on "autonumber" you'll find a lot of advice.

Autonumber is a field type that is really a long integer, with some

hidden
code
to increment the value for every added record. I do NOT recommend
any of these pseudoID forms that use combinations of

initials/date/other,
as they ALL will eventually fail and most are vulnerable to user

input
mistakes.
Trying to "imbed" data within an ID is almost always a bad idea.
Most good data designs have a primary key for each table,
generally an autonumber field. Most applications do not display that

ID, but
use it internally for relational integrity. If you want to show the

records
entered in any particular year,
you should be using a query to filter records based on a real date

range.

That said, one of the more common solutions for what you ask, is to
concatenate
2 fields in the format that you require. Keep the standard

autonumber
field, then add a [DateEntered] field
to your table and data entry form. Make this a date/time field type

and set
the default value to Date().
This will automatically enter the system date anytime a new record is

added.
On your forms/reports
that need the pseudoID displayed, create an unbound textbox and set

its
control source to something like:
=DatePart("yyyy",[DateEntered])&[AutonumberFieldName]

-Ed


"the hotshot" <ho*****@inbox.lv> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
> hello,
>
> this seems to be a hard question so far and noone has been able to help > with this. is it possible to have access start an autonumber with a
> prefix according to the year when the data is entered. for example, if > i entered something in 2004, i would like the number to bigin with
> 2004003, 2004004, 2004005... and same for 2005001, 2005002...?
> much and great appreciation for suggestions.
>
> thank you,
>
> the hotshot
>

Nov 13 '05 #4
Thank you so much Ed. I received more help then anticipated. I still
need to fogure out how to create the unbound text box - all of this is
new to me. Will try to do in the next few days.

Thanks!

Ed Robichaud wrote:
Sure. Unbound text boxes are just a text box control on a form/report that are not "bound" to a specific field in a data table/query. To create one, do not drag and drop from the field list. Use the toolbox wizard to place a text box on your form. In the control source property, put the expression: =DatePart("yyyy",[DateEntered])&"-"&[AutonumberFieldName]

(assuming that you have bound controls on your form, named
"DateEntered" -bound to a date/time field in your table/query that has a default value of Date() - and "AutonumberFieldName" bound to the primary key of your table/query - more likely called something like CustomerID)
This will then display (but not store in a table) the combined values -i.e. "2004-001", "20043-002", etc. You would need to copy that control to any form/report that needs to display the same info.
-Ed

"the hotshot" <ho*****@inbox.lv> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
thank you for the great advice Ed. i cant pass the stage of creating an unbound textbox. maybe it would be possible to also help with this
step? i tried looking on the net on how to do it, but there are no
clear explanations, only definitions of it.

thanks again,

the hotshot
Ed Robichaud wrote:
If you search this group on "autonumber" you'll find a lot of advice.
Autonumber is a field type that is really a long integer, with some
hidden
code
to increment the value for every added record. I do NOT recommend
any of these pseudoID forms that use combinations of

initials/date/other,
as they ALL will eventually fail and most are vulnerable to user

input
mistakes.
Trying to "imbed" data within an ID is almost always a bad idea.
Most good data designs have a primary key for each table,
generally an autonumber field. Most applications do not display
that ID, but
use it internally for relational integrity. If you want to show
the records
entered in any particular year,
you should be using a query to filter records based on a real date

range.

That said, one of the more common solutions for what you ask, is
to concatenate
2 fields in the format that you require. Keep the standard

autonumber
field, then add a [DateEntered] field
to your table and data entry form. Make this a date/time field type and set
the default value to Date().
This will automatically enter the system date anytime a new record
is added.
On your forms/reports
that need the pseudoID displayed, create an unbound textbox and
set its
control source to something like:
=DatePart("yyyy",[DateEntered])&[AutonumberFieldName]

-Ed


"the hotshot" <ho*****@inbox.lv> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
> hello,
>
> this seems to be a hard question so far and noone has been able
to help
> with this. is it possible to have access start an autonumber
with a > prefix according to the year when the data is entered. for example, if
> i entered something in 2004, i would like the number to bigin

with > 2004003, 2004004, 2004005... and same for 2005001, 2005002...?
> much and great appreciation for suggestions.
>
> thank you,
>
> the hotshot
>


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by oo00oo | last post: by
7 posts views Thread by Eddie | last post: by
6 posts views Thread by Sebastien | last post: by
4 posts views Thread by petermichaux | last post: by
8 posts views Thread by John | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.