Solved Dlookup null value

[Momma]

Hi there
I have a Dlookup to get the value of the CurrentInvoiceID but it returns with a Type Mismatch error. All the fields are numeric and CurrentInvoiceID
and CurrentContactID have been declared as long.
I also need to add NZ() for null values.

Any help is highly appreciated.
Thanks

CurrentInvoiceID = DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid) & " and dogid = & 0"

 

[Josef P.]

Code broken down into several steps:

Dim CriteriaString as String
Dim LookupResult as Variant

CriteriaString = "ContactID=" & CurrentContactid
LookupResult = DLookup("InvoiceID", "tblInvoices", CriteriaString)
CurrentInvoiceID =  LookupResult & " and dogid = & 0"

Is the logic correct?

 

[Momma]

Code broken down into several steps:

Dim CriteriaString as String
Dim LookupResult as Variant

CriteriaString = "ContactID=" & CurrentContactid
LookupResult = DLookup("InvoiceID", "tblInvoices", CriteriaString)
CurrentInvoiceID =  LookupResult & " and dogid = & 0"

Is the logic correct?

I'm still getting a Type Mismatch on the last line.

 

[isladogs]

I'm not sure why the dogid part is included.
Why not just use
CurrentInvoiceID = Nz(DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid),0)

 

[Josef P.]

I'm still getting a Type Mismatch on the last line.

You don't see the mistake?
The code in #2 reproduces your code - just split into multiple lines to make it easier to see the error. ... at least that's how I had hoped.

Tip: NumericVariable = NumericVariable & String

 

[Momma]

I'm not sure why the dogid part is included.
Why not just use
CurrentInvoiceID = Nz(DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid),0)

I'm sorry, the zero at the end should be CurrentDogID, but my syntex is wrong. The invoice should have both the CurrentContactID and the CurrentDogID. There could be another invoice for the same ContactID with a different Dogid.
CurrentInvoiceID = DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid) & " and Dogid = & CurrentDogID"

 

[isladogs]

Josef has explained your problem. Look again at the string after the DLookup
For example if the InvoiceID= 1234 and CurrentDogID=999 then your expression becomes 1234 and Dogid = 999 which is a string not a long integer

Try CurrentInvoiceID = DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid) & Nz(CurrentDogID,0)

 

[cheekybuddha]

I think you just have you bracket in the wrong place ( which is what I think Josef P is trying to make you see).

Maybe something more like:

CurrentInvoiceID = Nz(DLookup("InvoiceID", "tblInvoices", "ContactID=" & CurrentContactid & " and dogid = 0"), 0)

 

[Josef P.]

... which is what I think Josef P is trying to make you see

I wanted to trigger an "Oh! Now I see it too" experience.

Basic recommendation: a few lines more in the code do not disturb and it becomes clearer to recognize the individual steps.
1. create criteria string
2. run DLookup
3. do something with the result of DlookUp
=>

Dim CriteriaString as String
Dim LookupResult as Variant

CriteriaString = "ContactID=" & CurrentContactid & " and dogid=0"
debug.print CriteriaString '<-- to check string

LookupResult = DLookup("InvoiceID", "tblInvoices", CriteriaString)
CurrentInvoiceID =  nz(LookupResult, 0)
' or:
' if isnull(LookupResult) then
'    err.raise ..
' end if
'CurrentInvoiceID = LookupResult

 

[isladogs]

Hi Josef
Looks like I misinterpreted what the OP wanted when I gave a specific solution. Sorry about that ...

 

[Josef P.]

[OT]
Hi Colin,
no worries, my plan failed anyway.

 

[Momma]

[OT]
Hi Colin,
no worries, my plan failed anyway.

Your plan worked, Josef. Thank you so much!