Dlookup help?

[TomJamieson]

Hi,

I've searched through previous dlookup threads, but nothing seems to work. I have the following code:

If IsNull(DLookup("[Type of QC 2]", "Priorities", [ID] = tempID)) And IsNull(DLookup("[Type of QC 3]", "Priorities", [ID] = tempID)) Then
Forms.frmQCReport.txtQCID = 1
End If

If Not IsNull(DLookup("[Type of QC 2]", "Priorities", [ID] = tempID)) And IsNull(DLookup("[Type of QC 3]", "Priorities", [ID] = tempID)) Then
Forms.frmQCReport.txtQCID = 2
End If

If Not IsNull(DLookup("[Type of QC 2]", "Priorities", [ID] = tempID)) And Not IsNull(DLookup("[Type of QC 3]", "Priorities", [ID] = tempID)) Then
Forms.frmQCReport.txtQCID = 3
End If


What it's doing is checking through the 3 fields in the "Priorities" table. According to how many of those are filled in, it populates the txtQCID field in the frmQCReport form with the appropriate number. If just the first field is entered (Type of QC 1) then the QCID is 1, if the first and second are filled, it is 2, and if all 3 are filled, it is 3.

I can't see what is wrong with my code, but it populates the txtQCID field with 2 every time.

Can anyone help?

 

[RuralGuy]

Maybe this link will shed some light on the issue.

 

[TomJamieson]

Nope, I've already looked up all the syntax, and I think it's right anyway, because the code is not erroring. It's just not producing the correct results.

 

[RuralGuy]

I'm pretty sure the Criteria is supposed to be a string but if you say it is ok then...

 

[TomJamieson]

Oh sorry, I left that part of the code out. tempID is an integer variable that is assigned a number earlier on in the code. The page you linked to says it can be a number, a string or a date, doesn't it?

 

[gemma-the-husky]

ruralguy is right

your criteria [ID] = tempID
should be

"[id] = " & tempid if numeric or
"[id] = " & chr(34) & tempid & chr(34) if text
"[id] = " & "#" & tempid & "#" if a date

 

[TomJamieson]

Ooooh I get it. I'll give it a try.

 

[TomJamieson]

It worked! Thank you