DLookup not recognizing variable

[Don Barton]

I am using MS Access 2007 with Vista home premium
The user selects a value (string) from a combobox. Hitting tab to the
next control fires an on Got Focus event.

Dim TestMnem As String, TestName As Variant

TestMnem = Me.[LOTestMnemonic]

TestName = DLookup("[LabTestName]", "tblLabTests", "[LabTestMnemonic]
= " & TestMnem)

Me.LOTestName = TestName

TestMnem returns the desired string value as selected on my form. Let
say it is "CHEMSC"
Next I use this the variable TestMnem in the DLookup criteria
expression above.
I get the following error each time: "The expression you entered as a
query parameter produced this error: *value of TestMnem*

From everything I've looked at this should work. If I change
"[LabTestMnemonic] = " & TestMnem) to "[LabTestMnemonic] =
'CHEMSC'"), the event works correctly.


Don
LabDBDesigns

 

[Don Barton]

I am using MS Access 2007 with Vista home premium
The user selects a value (string) from a combobox.  Hitting tab to the
next control fires an on Got Focus event.

Dim TestMnem As String, TestName As Variant

TestMnem = Me.[LOTestMnemonic]

TestName = DLookup("[LabTestName]", "tblLabTests", "[LabTestMnemonic]
= " & TestMnem)

Me.LOTestName = TestName

TestMnem returns the desired string value as selected on my form. Let
say it is "CHEMSC"
Next I use this the variable TestMnem in the DLookup criteria
expression above.
I get the following error each time: "The expression you entered as a
query parameter produced this error: *value of TestMnem*

From everything I've looked at this should work.  If I change
"[LabTestMnemonic] = " & TestMnem) to "[LabTestMnemonic] =
'CHEMSC'"), the event works correctly.

Because LabTestMnemonic is a Text field in its table, you
need to end up with quotes around the value in the
comparison:

TestName = DLookup("LabTestName", "tblLabTests",
"LabTestMnemonic = """ & TestMnem & """")

I removed the unecessary  [ ] just to make it easier to
read.

The reason for so many quotes is because a " inside "s has
to be typed as "".  If the values in LabTestMnemonic can
never contain an apostrophe, you could use this instead:

TestName = DLookup("LabTestName", "tblLabTests",
"LabTestMnemonic = '" & TestMnem & "' ")

Thanks Marsh,

I've haven't been doing any development in MS Access for about 2 years
now, and the above explaination makes perfect sense.
It feels good to get back into it.
I had forgotten how "interesting" DLookup's usage of "'/"" to set off
text string variable criteria inside a text string (criteria
expression) was.
It would have been most helpful if the MS Access Help (F1) for DLookup
included a string criteria example. Perhaps it is not considered good
practice perhaps?
It works great none the less,

Thanks again.

Don
LabDBDesigns

 

[Jason]

then add the quotes as below assuming TestMnem is string not numeric

TestName = DLookup("[LabTestName]", "tblLabTests", "[LabTestMnemonic] = """ & TestMnem & """)

TOO MANY QUOTED LINES

 

[Jason]

then add the quotes as below assuming TestMnem is string not numeric
On 30/12/2010 11:46, Don Barton wrote:
TestName = DLookup("[LabTestName]", "tblLabTests", "[LabTestMnemonic]
= """ & TestMnem & """")