Dlookup with multiple criteria

Topic

New Reply

Hi all,

I am looking for a little syntax advice when using multiple criteria in a dlookup function. I have the following VBA:

Me![Text228] = DLookup(“[CLIN_DESCRIP]”, “Cams_Piin_Data2”, “[PIIN_CODE] = ‘” & Forms![supmod -> Mod]![PIIN_CODE] & “‘” And “[CLIN] = ‘” & Me![CLIN] & “‘”)

It will compile with no error but when run I get a run time error 13( type mismatch). Both PIIN_CODE and CLIN are text fields in the table.

If i drop one of the criteria it works fine so I thought I could just add an AND with a second criteria but it appears that there is more to it than that.

Can someone point me in the right direction?

Thanks you
Kevin

Reply | Quote

Replies

Hi all,

I am looking for a little syntax advice when using multiple criteria in a dlookup function. I have the following VBA:

Me![Text228] = DLookup(“[CLIN_DESCRIP]”, “Cams_Piin_Data2”, “[PIIN_CODE] = ‘” & Forms![supmod -> Mod]![PIIN_CODE] & “‘” And “[CLIN] = ‘” & Me![CLIN] & “‘”)

It will compile with no error but when run I get a run time error 13( type mismatch). Both PIIN_CODE and CLIN are text fields in the table.

If i drop one of the criteria it works fine so I thought I could just add an AND with a second criteria but it appears that there is more to it than that.

Can someone point me in the right direction?

Thanks you
Kevin

It is hard to read, but I think you have a problem with apostrophes in the WHERE portion. It should be:

, “[PIIN_CODE] = ‘” & Forms![supmod -> Mod]![PIIN_CODE] & “‘ And [CLIN] = ‘” & Me![CLIN] & “‘”)

or this:

, “[PIIN_CODE] = ” & chr(34) & Forms![supmod -> Mod]![PIIN_CODE] & chr(34) & ” And [CLIN] = ” & chr(34) & Me![CLIN] & chr(34))

(note: chr(34) is a double-quote.

Reply | Quote

It is hard to read, but I think you have a problem with apostrophes in the WHERE portion. It should be:

, “[PIIN_CODE] = ‘” & Forms![supmod -> Mod]![PIIN_CODE] & “‘ And [CLIN] = ‘” & Me![CLIN] & “‘”)

or this:

, “[PIIN_CODE] = ” & chr(34) & Forms![supmod -> Mod]![PIIN_CODE] & chr(34) & ” And [CLIN] = ” & chr(34) & Me![CLIN] & chr(34))

(note: chr(34) is a double-quote.

Thank you,

That worked perfectly. Looks like I had a few too many quotes.

Reply | Quote