generating auto number with a specific prefix

[the hotshot]

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

[Ed Robichaud]

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

[the hotshot]

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

[Ed Robichaud]

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
>

[the hotshot]

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
>