how to let a field count +1

[keesb]

I have a query in witch a want one field count +1
It is based on a tabel
The field is called InvoiceNumber in my tabel Invoices

so if the last value is 45 the next one must be 46.
My query must do this for a lot of records in my table.

I tried to use Dmax () but when my last number is 45 and I run my query, all
the records get the number 46.

I dont want to use AutoNumber.

How can I solve this?

regards

Kees

 

[JohnFol]

DMax has an argument as a where condition. You can do "IDField<" &
IDFieldInQuery

 

[keesb]

Thanks voor your replay,

I don't understand something:

It must be something like this ?
Expres: DMax("[Faktuurnummer]";"FaktuurNummer";"IDField <" & IDFieldInQuery)

But what field must I use for IDField en witch field for IDFieldInQuery ?



"IDField<" &

 

[JohnFol]

Hi Kees,

Re-reading your question and I think you are after 1 of 2 things. Do you
want a query to show the numbers 1, 2, 3 . . . and headed InvoiceNumber, or
do you want the values 1, 2, 3 . .entered into the Invoices table when you
add a new record?

If you want the numbers in the query:

Select DCount("*";"Invoices";"SortField <" & [SortField])
as InvoiceNumber, SortField, Field1, Field2 from Invoices order by
SortField

(where SortField is the name of the field you want the list ordered by, and
Field1, Field2 are just other fields. )

If you want a new entry in the Invoices table to store the value:

Don't worry about this query, but add a piece of code on the form you
use to enter Invoices to the Invoice table. On the Before_Update, do a

InvoiceNumber = DMax("InvoiceNumber", "Invoices") + 1

I hope this helps

Thanks voor your replay,

I don't understand something:

It must be something like this ?
Expres: DMax("[Faktuurnummer]";"FaktuurNummer";"IDField <" &
IDFieldInQuery)

But what field must I use for IDField en witch field for IDFieldInQuery ?



"IDField<" &

IDFieldInQuery

DMax has an argument as a where condition. You can do "IDField<" &
IDFieldInQuery