Sequential number formula explanation

[ramondrumon]

I found this formula: =DMax("InvoiceID","Invoice&quot+1, where Invoice is my table and invoice ID my number field. It works good, I just can't find out how it works. Whatis DMax and why "INvoiceID","Invoice" and the quotation marks. Also, can you have it control the number at the bottom where you scroll the different forms. If you delete an invoice it does the same thing as auto-number and doesn't match up any more. This invoice number thing is being a pain in the arse! Thanks to all those people that look in here and can help.

 

[danvlas]

DMax("FieldName", "TableOrQueryName", ["Criteria"]) is a domain aggregate function that finds the maximum value in a field from a domain (table or query). The domain can be 'reduced' by using a Where clause that filters initial records retrieved from the domain.
So, in your example, InvoiceID is the name of the field, Invoice is the name of the domain (again, can be a table or a query). Why quotation marks? Because that's the way it is. Without quotation marks, you can pass values from string variables in VBA.

"If you delete an invoice it does the same thing as auto-number..."

I disagree... If you use AutoNumber and delete the last record, that record ID will not be assigned anymore (unless you compact the database). Using DMax, you will generate the number again next time, whether you compact the database or not.

AutoNumber is not suitable for invoice numbers, because you'll get gaps in numbering. Whenever you cancel inserting a new record, the AutoNumber is anyway incremented, so that number won't be assigned...
DMax could be suitable if you do NOT delete the last (which in our case is the Max) invoice.
If you need both to ensure a continuous sequence and delete records without generating the same number again, use the method suggested by Ben O'Hara in Thread181-341170.

Regards

Daniel Vlas
Systems Consultant
danvlas@yahoo.com