Syntax error in DLookup expression

[Pamela]

I am getting a syntax error that there's a missing operator in query
expression 'ShopID ='. From this it almost seems that it isn't reading the
True statement in our expression which should disregard that "false" part of
the expression altogether. Any ideas how to fix this? Here's the expression:
IIf(IsNull(Me.ShopName), "The owner has not yet chosen a repair shop.", _
DLookup("Expr1", "qryShopReturn", "ShopID = " & Me!ShopName) & " ").
ShopName is a cbo that is populated by tblShop where ShopID is bound but
ShopName is displayed. Thanks for your help!
Pamela

 

[Douglas J. Steele]

You need to tie all the separate pieces of advice you've been getting
together. (This is best accomplished by continuing to post in the same
thread, rather than constantly starting new one!)

Is ShopID text? If so, you need

DLookup("Expr1", "qryShopReturn", "ShopID = '" & Me!ShopName & "'")

 

[Beetle]

What is the data type of ShopID in tblShop
(i.e. number, text)?

 

[Pamela]

ShopID is AutoNumber (pk). I hope this helps you help me! : ) Thanks!

 

[John W. Vinson]

I am getting a syntax error that there's a missing operator in query
expression 'ShopID ='. From this it almost seems that it isn't reading the
True statement in our expression which should disregard that "false" part of
the expression altogether. Any ideas how to fix this? Here's the expression:
IIf(IsNull(Me.ShopName), "The owner has not yet chosen a repair shop.", _
DLookup("Expr1", "qryShopReturn", "ShopID = " & Me!ShopName) & " ").
ShopName is a cbo that is populated by tblShop where ShopID is bound but
ShopName is displayed. Thanks for your help!
Pamela

The problem is that the IIF statement (sometimes, not always!) evaluates both
the True and False branches. Try a different approach like

IIf(IsNull(Me.ShopName), "The owner has not yet chosen a repair shop.",
DLookup("Expr1", "qryShopReturn", "ShopID = " & NZ(Me!ShopName))

If there is nothing selected in the combo box ShopName this will return NULL
from the DLookUp (actually it will return the shopname for ShopID equal to 0,
which I assume does not exist).

 

[Jeff Boyce]

Pamela

Why are you trying to lookup something when the ShopID = (something that
seems like it would be text -- ShopName)?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Pamela]

Thanks, John! The system didn't like that expression in there either. But,
I figured another way to do it using my query: I wrote the expression in my
query, added and joined my form's underlying table to ltblShop and used
AssnNumber as the Lookup which is has a one-to-one relationsthip with all of
my forms/subforms.

Thanks!!!