Use of query to add sequential numbers to a table.

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

Frank Martin

My InvoiceLines table uses the autonumber field for the primary key.

But I need another auto-incrementing field for the sort field.

Can such a field be genererated in the query by means of an expression, and
if so which one?

Please help, Frank
 
Are you asking if you can dynamically generate a value/column in a query or
do you think you need to store the value? Do you have some field or fields
that will determine the new sort order?
 
Frank,

Use the DMAX function, in conjunction with the NZ function, as below:

NZ(DMAX("SortOrder", "yourTable"), 0) + 1

This looks for the maximum value in the SortOrder field, then adds one to
it. If there are no valid entries in SortOrder, DMAX will return a NULL, so
you have to use NZ to convert that to a zero, before the addition.

HTH
Dale
 
Thanks, I'll try this.


Dale Fye said:
Frank,

Use the DMAX function, in conjunction with the NZ function, as below:

NZ(DMAX("SortOrder", "yourTable"), 0) + 1

This looks for the maximum value in the SortOrder field, then adds one to
it. If there are no valid entries in SortOrder, DMAX will return a NULL, so
you have to use NZ to convert that to a zero, before the addition.

HTH
Dale
Click to expand...
 
I need something to act as an 'autonumber', in the field of my choice, even
when the PK is an autonumber.

I need it for the 'sorting field' and also for incrementing invoice numbers.
 
Back
Top