A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dlookup Expression Error



 
 
Thread Tools Display Modes
  #1  
Old December 29th, 2009, 11:30 PM posted to microsoft.public.access.queries
Pamela
external usenet poster
 
Posts: 193
Default Dlookup Expression Error

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!

  #2  
Old December 30th, 2009, 01:29 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Dlookup Expression Error

On Tue, 29 Dec 2009 14:30:01 -0800, Pamela
wrote:

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]

  #3  
Old December 30th, 2009, 03:06 AM posted to microsoft.public.access.queries
Pamela
external usenet poster
 
Posts: 193
Default Dlookup Expression Error

That did it! Thank you so much!

"John W. Vinson" wrote:

On Tue, 29 Dec 2009 14:30:01 -0800, Pamela
wrote:

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]

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.