Dlookup with where conditions

[Guest]

Hi

I have a query that picks up various pieces of data from tblEstimate. One of
these fields is EstimateNo

I have another table called tblInvoiceNumbers, there are only two fields in
tblInvoiceNumbers - EstimateNo and InvoiceNo, all fields described are number
fields. There is a relationship between the fields EstimateNo in both tables
described.

I wish to write an expression in qryInvoice that looks up values in
tblInvoiceNumbers based upon the EstimateNo and if there is no matching data
with EstimateNo then I want to Dmax +1 the highest number within the
InvoiceNo field in tblInvoiceNumbers, however if there is a invoice number
already recorded against the estimate number then I wish to use that invoice
number.

So a brief summary how I see it is

Iff statement regarding using any existing record in tblInvoiceNumbers which
incorporates the DLookup and Dmax statements.

If anyone can understand this and thinks that I may be doing the wrong thing
could you please advise a better way

 

[Wolfgang Kais]

Hello richard.

Hi

I have a query that picks up various pieces of data from
tblEstimate. One of these fields is EstimateNo

I have another table called tblInvoiceNumbers, there are only two
fields in tblInvoiceNumbers - EstimateNo and InvoiceNo, all fields
described are number fields. There is a relationship between the
fields EstimateNo in both tables described.

I wish to write an expression in qryInvoice that looks up values
in tblInvoiceNumbers based upon the EstimateNo and if there is no
matching data with EstimateNo then I want to Dmax +1 the highest
number within the InvoiceNo field in tblInvoiceNumbers, however if
there is a invoice number already recorded against the estimate
number then I wish to use that invoice number.

So a brief summary how I see it is

Iff statement regarding using any existing record in
tblInvoiceNumbers which incorporates the DLookup and Dmax
statements.

If anyone can understand this and thinks that I may be doing the
wrong thing could you please advise a better way

Always have in mind that the D-something functions are very slow.
The DLookup function will look like this:
DLookup("InvoiceNo", "tblInvoiceNumbers", "EstimateNo=" & EstimateNo)
and the DMax function will be
DMax("InvoiceNo", "tblInvoiceNumbers") + 1
The Iif function also has a disadvantage: All arguments are are
evaluated. Since your expression would look something like this:
Iif(IsNull(DLookup(...), DMax(...)+1, DLookup(...)), the DLookup
expression would have to be calculated twice. I'd rather use the
Nz function: Nz(DLookup(...), DMax(...) + 1)
And what if multiple InvoiceNo exist for the EstimateNo?