Dlookup syntax

  • Thread starter Thread starter Claudette Hennessy
  • Start date Start date
C

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
 
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] & "'")
 
Claudette Hennessy said:
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
Click to expand...
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
 
Claudette said:
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.
Click to expand...


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
& """")
 
Thank you, that works.
Claudette
Marshall Barton said:
Claudette said:
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.
Click to expand...


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