How To Change AutoNumber Format

  • Thread starter Thread starter jmllr24
  • Start date Start date
J

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.
 
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
 
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.



Jeff Boyce said:
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 said:
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.
Click to expand...
Click to expand...
 
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


jmllr24 said:
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.



Jeff Boyce said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top