How To Change AutoNumber Format

[jmllr24]

Currently my database automatically assigns an ID number to every entry. So
right now every entry is given a numerical ID that goes in sequential order.

i.e 1, 2, 3 ,4, 5...

My form also asks a user to enter in a date.

Is there a way to link my autonumber to the year a user enters.

i.e I would like this ID number to be 09-01, 09-02, 09-03, 09-04, etc...

Thanks so much.

 

[Jeff Boyce]

Yes ... and no!

You can use a query to "concatenate" and format a combination of the
Year([YourDateField]) { or by formatting [YourDateField] to show only YY }
and a sequence number.

Now the bad news ...

If your database uses the Access Autonumber data type to assign that [ID],
it is NOT guaranteed to be sequential. Access Autonumbers are intended to
provide unique row identifiers, that's all.

And you don't describe what you want to have happen on January 1, 2010 ...
is the first record of the new year supposed to have "10-01", or "10-xx",
where xx is the next sequential number? What you need will constrain
"how"...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jmllr24]

Jeff, Thanks for responding back to my question.

My intentions would be for on January 1, 2010 to have the number be
10-01...i.e. every year it start over.

Thanks for your help. Please let me know if you need any more information.

Yes ... and no!

You can use a query to "concatenate" and format a combination of the
Year([YourDateField]) { or by formatting [YourDateField] to show only YY }
and a sequence number.

Now the bad news ...

If your database uses the Access Autonumber data type to assign that [ID],
it is NOT guaranteed to be sequential. Access Autonumbers are intended to
provide unique row identifiers, that's all.

And you don't describe what you want to have happen on January 1, 2010 ...
is the first record of the new year supposed to have "10-01", or "10-xx",
where xx is the next sequential number? What you need will constrain
"how"...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Currently my database automatically assigns an ID number to every entry.
So
right now every entry is given a numerical ID that goes in sequential
order.

i.e 1, 2, 3 ,4, 5...

My form also asks a user to enter in a date.

Is there a way to link my autonumber to the year a user enters.

i.e I would like this ID number to be 09-01, 09-02, 09-03, 09-04, etc...

Thanks so much.

 

[Jeff Boyce]

Try searching on-line for "Custom Autonumber" (yes, I know, it says
"autonumber", but it isn't, really).

Also try checking at:

www.mvps.org/access

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, Thanks for responding back to my question.

My intentions would be for on January 1, 2010 to have the number be
10-01...i.e. every year it start over.

Thanks for your help. Please let me know if you need any more information.

Yes ... and no!

You can use a query to "concatenate" and format a combination of the
Year([YourDateField]) { or by formatting [YourDateField] to show only
YY }
and a sequence number.

Now the bad news ...

If your database uses the Access Autonumber data type to assign that
[ID],
it is NOT guaranteed to be sequential. Access Autonumbers are intended
to
provide unique row identifiers, that's all.

And you don't describe what you want to have happen on January 1, 2010
...
is the first record of the new year supposed to have "10-01", or "10-xx",
where xx is the next sequential number? What you need will constrain
"how"...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Currently my database automatically assigns an ID number to every
entry.
So
right now every entry is given a numerical ID that goes in sequential
order.

i.e 1, 2, 3 ,4, 5...

My form also asks a user to enter in a date.

Is there a way to link my autonumber to the year a user enters.

i.e I would like this ID number to be 09-01, 09-02, 09-03, 09-04,
etc...

Thanks so much.