Use of query to add sequential numbers to a table.

[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

 

[Duane Hookom]

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?

 

[Dale Fye]

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

 

[Frank Martin]

Thanks, I'll try this.

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

 

[Frank Martin]

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.