ID

[Victoria]

Can someone please help me establish a unique order
number for each record entered into my table. It has to
be in a format of 04-xxxxxx. My biggest hurdle is I have
3 years of records in my table. I was told I can ignore
those records and start new but what will force the 04 to
change to 05 next year? I have been trying everything I
can and I am stumped.Please help.

Victoria

 

[Jeff Boyce]

Victoria

What you see displayed, and what you store are two separable things.

A wild guess, but the "04" (and "05") appear to be the "yy" part of a year
(didn't anyone learn anything about Y2K?!) ... (I design for LONG run!).

If your record/row has a date field, you can use the Year([YourDateField])
function to get the year portion, and if you insist, the
Right(Year([YourDateField]),2) functions to get the right-most two
characters.

Next, if your order number needs to reset to ###...1 each year, you'll need
to create a fairly simple routine to do that. Check either the mvps.org
website, or google.com (in the Access db area) for "custom number" or
"custom autonumber" to see examples.

Finally, build a query that you use to combine the pieces together into
"04-###...1", and use that query for your forms, reports, and other queries
that need to use the information.

 

[Tim Ferguson]

Can someone please help me establish a unique order
number for each record entered into my table. It has to
be in a format of 04-xxxxxx.

You have two bits of information here, and will therefore need two fields.
One will be the YearCreated (or something like that) and the other will be
the SerialNumber. It is easy enough to allocate the SerialNumber that
resets to 000001 every year, although you can't use an Autonumber. Try
googling for "Access Custom Autonumber" for loads of suggestions.

What the user sees is a single textbox control where you set its
ControlSource to

=Format(YearCreated,"00") & "-" & Format(SerialNumber,"000000")

My biggest hurdle is I have
3 years of records in my table. I was told I can ignore
those records and start new but what will force the 04 to
change to 05 next year?

As long as there is a date field that applies somewhere in the record, then
you can use an update query to fill in the YearCreated field with
Year(SomeDate). With new records, you can use a DefaultValue of
Year(Date()) or do it on the form.

Hope that helps


Tim F