use DMax in a query

[Pampers]

Hi folks,
I try to make a query with a Dmax in it. But I don't know how. This is what I come up with so far.

InvoiceNew: IIf([InvoiceNo] Is Null;DMax([InvoiceNo])+1;[InvoiceNo])

Pampers.

You're never too young to learn

 

[PHV]

Perhaps this ?
InvoiceNew: 1+Nz(DMax([InvoiceNo]),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, hit submit toofast :~/
Perhaps this ?
InvoiceNew: 1+Nz(DMax("InvoiceNo","yourTable"),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

Hi PHV,
tnx for the reply
It works, but my idea is wrong. I want to create unique invoice numbers when the rptInvoice is opened (based on qryInvoice. (Uptill now I assigned InvoiceNo in the form with DMax. It works, but when two users are entering InvoiceInformation, it will create duplicate InvoiceNo.)

Mabye it has to be an update query...
but doesn't work either. The udatequery takes the highest number adds 1, and put this number back for all the empty invoiceno...
Suggestions?


Pampers.

You're never too young to learn

 

[Pampers]

Does splitting the database (be - fe) solve such a problem?


Pampers.

You're never too young to learn