Dlookup Expression Error

P

Pamela

My query is based on a table that I use to populate a cbo on a form. My
table includes: ID(pk), ShopName, and ShopCity (which stores the ID # from
tblCity). My query has ShopName, ShopCity and an Expression field that I was
to use later. Everything in my expression works except for the DLookup which
is just blank.

Expr1: IIf([ShopName]="None","The owner did not have a choice of shop at the
time of my inspection.","The owner has chosen " & [ShopName] & " in " &
DLookUp("[City]","[ltblCity]","[CityID]"="[ShopCity]"))

Please help me to see what's wrong with this expression. Again, not getting
an error - but just a blank where that City name should be.

Thanks!
 
J

John W. Vinson

My query is based on a table that I use to populate a cbo on a form. My
table includes: ID(pk), ShopName, and ShopCity (which stores the ID # from
tblCity). My query has ShopName, ShopCity and an Expression field that I was
to use later. Everything in my expression works except for the DLookup which
is just blank.

Expr1: IIf([ShopName]="None","The owner did not have a choice of shop at the
time of my inspection.","The owner has chosen " & [ShopName] & " in " &
DLookUp("[City]","[ltblCity]","[CityID]"="[ShopCity]"))

Please help me to see what's wrong with this expression. Again, not getting
an error - but just a blank where that City name should be.

Thanks!

This will return the value of City where the CityID is the literal text string
"[ShopCity]". I very much doubt there is such a record!

Try

Expr1: IIf([ShopName]="None","The owner did not have a choice of shop at the
time of my inspection.","The owner has chosen " & [ShopName] & " in " &
DLookUp("[City]","[ltblCity]","[CityID]=" & [ShopCity]))

This will concatenate the *value* (presumably a number) of ShopCity to the
text string "[CityID] =" giving a result like

[CityID] = 3198

or whatever the shopcity value might be.
 
P

Pamela

That did it! Thank you so much!

John W. Vinson said:
My query is based on a table that I use to populate a cbo on a form. My
table includes: ID(pk), ShopName, and ShopCity (which stores the ID # from
tblCity). My query has ShopName, ShopCity and an Expression field that I was
to use later. Everything in my expression works except for the DLookup which
is just blank.

Expr1: IIf([ShopName]="None","The owner did not have a choice of shop at the
time of my inspection.","The owner has chosen " & [ShopName] & " in " &
DLookUp("[City]","[ltblCity]","[CityID]"="[ShopCity]"))

Please help me to see what's wrong with this expression. Again, not getting
an error - but just a blank where that City name should be.

Thanks!

This will return the value of City where the CityID is the literal text string
"[ShopCity]". I very much doubt there is such a record!

Try

Expr1: IIf([ShopName]="None","The owner did not have a choice of shop at the
time of my inspection.","The owner has chosen " & [ShopName] & " in " &
DLookUp("[City]","[ltblCity]","[CityID]=" & [ShopCity]))

This will concatenate the *value* (presumably a number) of ShopCity to the
text string "[CityID] =" giving a result like

[CityID] = 3198

or whatever the shopcity value might be.
--

John W. Vinson [MVP]

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top