DLookup not recognizing variable

  • Thread starter Thread starter Don Barton
  • Start date Start date
D

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 said:
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.
Click to expand...
Dim TestMnem As String, TestName As Variant
Click to expand...
TestMnem = Me.[LOTestMnemonic]
Click to expand...
TestName = DLookup("[LabTestName]", "tblLabTests", "[LabTestMnemonic]
= " & TestMnem)
Click to expand...
Me.LOTestName = TestName
Click to expand...
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*
Click to expand...
From everything I've looked at this should work.  If I change
"[LabTestMnemonic] = " & TestMnem) to "[LabTestMnemonic] =
'CHEMSC'"), the event works correctly.
Click to expand...

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 & "' ")
Click to expand...

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
 
then add the quotes as below assuming TestMnem is string not numeric
TestName = DLookup("[LabTestName]", "tblLabTests", "[LabTestMnemonic] = """ & TestMnem & """)
Click to expand...
TOO MANY QUOTED LINES
 
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 & """")
 
Back
Top