View Single Post
  #7 (permalink)  
Old October 17th, 2003, 05:23 PM
scott scott is offline
Authorized User
Join Date: Oct 2003
Location: New York, NY, .
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts

Jeff: I think this is more along the lines of what I need. Now to be really stupid... how/where should I insert the code you suggest?

My table is set up with the following fields:

ID (autonum; PK; used as FK in several other tables)
DateIn (default value: Date() )
TimeIn (default value: Time() )
.... there are several other fields, but not relevant to my original post...

What I would like to do is create a "new" field/form field that will show: (a) current date as in the DateIn column and (b) a sequential number (NOT the autonum in field [ID]) that will increment automatically with each new record added. When the date advances, the sequential portion of this new field should start numbering again at "1". It is unlikely I will need more than 3 digits in the sequential number.

What we are doing is tracking the number of "job requests" in each day. Some days are busy, others not... It is important to my boss that we can keep track "on the fly" of "what job number we are up to" during a particular day. This is not so much for reporting issues (I have a query to handle that issue already) but to make data entry easier for the persons using the DB in their daily tasks. Needless to say, no one in the department knows Access... so ease of use is essential.

What we do is enter the "job request number" on a requisition form. My boss does NOT want us to be entering "1,235" as a "job request number." We normally don't go over 50-100 jobs per day (though it has happened in the past that we will log 125-150 requests in one day!), thus I will need to have 3 digits in my sequential number. To keep all the information together, I would like this "automatically" generated sequential number to appear in the main data entry form that the users will use.

I will need the information to automatically populate when the record is begun, as well. (OnEnter?)

Anyway, thanks for your ideas: your help is GREATLY Appreciated!!

"Here, I think the OP wants to number the rows as they are being inserted. Perhaps something along the lines of:
VALUES (SELECT NZ(MAX(InvoiceNumber),0)+1 WHERE InvoiceDate=#invoicedate#),...)
The idea being to find the maximum value in the date in question and add 1 to get the next number. If the row is the first one on the given date, there will be no MAX value (it will be null), so the NZ protects against that...

(I'm no Access programmer, so I can't help with the forms display issue, but I do know a little about SQL so I might be able to help, query wise...)

Jeff Mason
Custom Apps, Inc.

Reply With Quote