Dlookup syntax

[Claudette Hennessy]

I need to look up a value to print in a report. Here is the expression, the
where clause is attempting to match to the value in a control on the report:
=DLookUp("[ReceiveContract]","tblContract2008","[ShowID] = 4 and [ShopName]
= '" & Me.txtShopname & "'")
The jet engine is objecting to "me". It doesn't like
Reports!rptContractlist![txtShopName] either. And I'm not sure I have the
single and double quotes right.

Thank you in advance,
Claudette Hennessy

 

[Ken Snell \(MVP\)]

This is an expression in a Control Source of a textbox, right? Just use the
name of the textbox without the Me:

=DLookUp("[ReceiveContract]","tblContract2008","[ShowID] = 4 and [ShopName]
= '" & [txtShopname] & "'")

 

[Matthias Klaey]

I need to look up a value to print in a report. Here is the expression, the
where clause is attempting to match to the value in a control on the report:
=DLookUp("[ReceiveContract]","tblContract2008","[ShowID] = 4 and [ShopName]
= '" & Me.txtShopname & "'")
The jet engine is objecting to "me". It doesn't like
Reports!rptContractlist![txtShopName] either. And I'm not sure I have the
single and double quotes right.

Thank you in advance,
Claudette Hennessy

Hm. Not sure about this, but you may want to try

=DLookUp("ReceiveContract","tblContract2008","ShowID = 4 and
ShopName = '" & Me!txtShopname & "'")

The single and double quotes seem to be ok.

There is no need to surround field names with [..].
Also, Me.txtShopname should work too.

What exactly is the error message that you get?

Greetings
Matthias Kläy

 

[Marshall Barton]

I need to look up a value to print in a report. Here is the expression, the
where clause is attempting to match to the value in a control on the report:
=DLookUp("[ReceiveContract]","tblContract2008","[ShowID] = 4 and [ShopName]
= '" & Me.txtShopname & "'")
The jet engine is objecting to "me". It doesn't like
Reports!rptContractlist![txtShopName] either. And I'm not sure I have the
single and double quotes right.

Me is only meaningful in a VBA class module procedure.

The full reference should work, but if txtShopName is in the
same report (not a subreport), you can just use txtShopname

However, if there's any chance that txtShopname might
contain the ' character, you can use:

. . ., "[ShowID] = 4 and [ShopName] = """ & Me.txtShopname
& """")

 

[Claudette Hennessy]

Thank you, that works.
Claudette

I need to look up a value to print in a report. Here is the expression,
the
where clause is attempting to match to the value in a control on the
report:
=DLookUp("[ReceiveContract]","tblContract2008","[ShowID] = 4 and
[ShopName]
= '" & Me.txtShopname & "'")
The jet engine is objecting to "me". It doesn't like
Reports!rptContractlist![txtShopName] either. And I'm not sure I have the
single and double quotes right.

Me is only meaningful in a VBA class module procedure.

The full reference should work, but if txtShopName is in the
same report (not a subreport), you can just use txtShopname

However, if there's any chance that txtShopname might
contain the ' character, you can use:

. . ., "[ShowID] = 4 and [ShopName] = """ & Me.txtShopname
& """")