View Single Post
  #3 (permalink)  
Old October 17th, 2003, 02:36 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

That referenced topic dealt with a numbering of the rows after the fact. That is, after the rows had already been inserted in the table (or at least that's how I interpreted the issue).

Here, I think the OP wants to number the rows as they are being inserted. Perhaps something along the lines of:
Code:
INSERT INTO Invoices
    (InvoiceNumber,...)
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.
www.custom-apps.com
Reply With Quote