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

 

[Marshall 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 & "' ")

 

[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

 

[Marshall 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 & "' ")

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,

I think many help examples assume you have read and
understood the implications of all other components of
Access so you can generalize them to their fullest extent
for use in your specific situation. After all, anyone with
at least 10 years of programming in several different
languages and systems will instantly know how to utilize all
facets of what the examples demonstrate so clearly ;-)

Glad to have helped get you going.

 

[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 & """")