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

On Dec 29, 5:55*pm, Marshall Barton <marshbar...@wowway.com> wrote:
> Don Barton wrote:
> >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 & "' ")
>
> --
> Marsh
> MVP [MS Access]

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
On 30/12/2010 11:46, Don Barton wrote:
> TestName = DLookup("[LabTestName]", "tblLabTests", "[LabTestMnemonic] = """ & TestMnem & """)
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 & """")
> TOO MANY QUOTED LINES