how do i force access to reset autonumber to 01 each day?

[tlskimi]

Access 2003 (which opens reading (Access 2000 file format) in the database
title bar....what does that mean?)
i'm creating a database for land survey orders. the job number for each
order is the date in yymmdd plus .00 in sequence. the third order today would
be job #050729.03. i want the next job number to be created automatically
each time the "new order" form is opened. can you help?

[David Mueller]

My first thought is "think of another way to number your jobs." Storing two
values in a single field isn't good practice.

Even so, if you're set on starting a sequence over at zero you will probably
need to read from a table of existing job numbers, counting how many exist
for that day, and adding one. This isn't fool proof: multiple users can snag
the same sequence if you are not careful. Being careful means you will
"waste" sequences.... User may grab 04, next user may grab 05, 04 order may
not get completed, the next order will be 06.

I'd suggesting using two fields ORDER_DATE, and ORDER_SEQUENCE. Populate
ORDER_SEQUENCE with the Timer() function - it returns the amount of seconds
past since midnight - so obviously it starts at zero every day, but again,
that is not sequential - it's chronological.

You may want to throw in an ORDER_ID (autonumber) primary key. It will be
more manageable than the composite key you are trying to create.

[tlskimi]

Unfortunately, this numbering system has been in use at this company for
15yrs and they are not about to change. I set up a NUMBERING table with 3
fields, the ORD_DATE returns the current system date, the ORD_SEQ I set up as
autonumber 2 digits. I was planning on combining them later in the process.
Then a TRIGGER field to allow the next record to be generated. Generally I
planned to have users enter something in the trigger field creating a new
record in the NUMBERING table, then have that record combined and formatted
by macro and returned to a JOB # field in a different table with the order
info. Is this too far fetched??

[John Vinson]

On Fri, 29 Jul 2005 14:17:01 -0700, "tlskimi"
<tls...@discussions.microsoft.com> wrote:

>Unfortunately, this numbering system has been in use at this company for
>15yrs and they are not about to change. I set up a NUMBERING table with 3
>fields, the ORD_DATE returns the current system date, the ORD_SEQ I set up as
>autonumber 2 digits. I was planning on combining them later in the process.
>Then a TRIGGER field to allow the next record to be generated. Generally I
>planned to have users enter something in the trigger field creating a new
>record in the NUMBERING table, then have that record combined and formatted
>by macro and returned to a JOB # field in a different table with the order
>info. Is this too far fetched??

An Autonumber field will not work for you: autonumbers are unique
within the table and not controllable. Just use an Integer field for
ORD_SEQ.

With David's cautions in mind, you can control the incrementing of
ORD_SEQ using VBA code in the Form. Access JET tables do not support
triggers, so that's not an option. I would urge that you NOT have a
separate NUMBERING table - it just makes things more complex; if you
change your current main table to use the Ord_Date and Ord_Seq fields
as a joint two-field Primary Key, you can concatenate them as needed
to display the Job #. You can use the form's BeforeInsert event. Have
a textbox named txtOrd_Seq on the form bound to the Ord_Seq field:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtOrd_Seq = NZ(DMax("[Ord_Seq]", "[Orders]", "[Ord_Date] = #" _
& Date() & "#")) + 1
End Sub

This assumes that the Ord_Date always corresponds to the calendar date
of data entry - if this is not (or ever might not be) the case,
instead use the Afterupdate event of the date field.

John W. Vinson[MVP]

[pere...@jetemail.net]

tlskimi wrote:
> Unfortunately, this numbering system has been in use at this company for
> 15yrs and they are not about to change.

You appear to have a 'calculated' column i.e. the date concatenated
with a sequence number of jobs for that day. All this can be generated
from a single DATE/TIME value and just about everyone here agrees a
calculated column shouldn't be persisted in a table. So why not make
the date+time be the _real_ key and 'fake' the legacy job number e.g.

CREATE TABLE Test (
key_col DATETIME DEFAULT NOW()
NOT NULL PRIMARY KEY,
data_col VARCHAR(255) NOT NULL);

INSERT INTO Test
VALUES (#2005-08-01 09:00:00#, 'One');

INSERT INTO Test
VALUES (#2005-08-01 15:00:00#, 'Three');

INSERT INTO Test
VALUES (#2005-08-01 12:00:00#, 'Two');

INSERT INTO Test
VALUES (#2005-08-02 12:00:00#, 'Four');

SELECT '#' & FORMAT$(T1.key_col, 'yymmdd')
& '.' & FORMAT$((
SELECT COUNT(*)
FROM Test
WHERE
FORMAT$(T1.key_col, 'yyyymmdd')
= FORMAT$(key_col, 'yyyymmdd')
AND key_col <= T1.key_col
), '00') AS job_nbr,
data_col AS job_data
FROM Test AS T1

You could even put

CREATE VIEW Jobs AS

on the front (or put into a Query) to hide the complexity from the DB
user.