Help with If Statement in query using a Dlookup

I need to display the state name within a query only when a field( [tblCountryInteractionInfo]![RequiresState] = Y/N) in separate table Equals yes, but still display all cities involved in the results.


SELECT tblTC.CityRecID, tblCountry.CountryName, tblCountry.CountryCode, tblTC.EffectiveDate, tblTC.EndDate, tblTC.CollectedbyName, tblTC.ConfidenceLevel, tblCurrency.CurrencyCode, tblCity.TaxInclusive, IIf([tblCountryInteractionInfo]![RequiresState]<>0,DLookUp("StateRecID","tblCity",[tblCity]![CountryRecID]=[tblCountryInteractionInfo]![CountryRecID]),"") AS State
FROM ((tblCurrency RIGHT JOIN tblTC ON tblCurrency.CurrencyRecID = tblTC.CurrencyRedID) LEFT JOIN (tblCountry RIGHT JOIN tblCity ON tblCountry.CountryRecID = tblCity.CountryRecID) ON tblTC.CityRecID = tblCity.CityRecID) LEFT JOIN tblCountryInteractionInfo ON tblCountry.CountryRecID = tblCountryInteractionInfo.CountryRecID
GROUP BY tblTC.CityRecID, tblCountry.CountryName, tblCountry.CountryCode, tblTC.EffectiveDate, tblTC.EndDate, tblTC.CollectedbyName, tblTC.ConfidenceLevel, tblCurrency.CurrencyCode, tblCity.TaxInclusive, IIf([tblCountryInteractionInfo]![RequiresState]<>0,DLookUp("StateRecID","tblCity",[tblCity]![CountryRecID]=[tblCountryInteractionInfo]![CountryRecID]),"");

Open in new window

The results should include where two records for Country = Canada, display the state/province as Ontario (ON)

The if statement works as far as the query runs, but it still isn't displaying the two records where State = ON.

What am I missing.?

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Your sample doesn't show the values for RequiresState.  You can try changing the <>0 to = True.  That will fix the problem if RequiresState has null values and you want to treat them as False.

IIf([tblCountryInteractionInfo]![RequiresState] =True,DLookUp("StateRecID","tblCity",[tblCity]![CountryRecID]=[tblCountryInteractionInfo]![CountryRecID]),"")

I would also not recommend using domain functions in queries.  Each domain function runs a separate query so if your query returns only 100 rows, you are running 100 unnecessary queries.  If it returns a thousand rows, you are running a thousand unnecessary queries.  In almost all cases (and this is one), DLookup() can be replaced by a Left Join to your lookup table.  Then you can format the state field as:
Select ...., IIf(tblCountryInteractionInfo.RequiresState =True, tblCity.StateRecID, Null) As StateName

A further question would be - is StateRecID actually the name of your state?  Because that's what the IIf() is returning.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
1.  Is there a chance that the [RequiresState] field is NULL?

2.  Are you certain that tblCountryInteractionInfo!CountryRecID is a valid value (it looks like it is numeric) in your table for those records?

3.  I'm guessing that the DLOOKUP portion of this needs to look like:

DLookUp("StateRecID","tblCity", "[CountryRecID]=" &[tblCountryInteractionInfo]![CountryRecID])

so the total computed column would look like:

IIf([tblCountryInteractionInfo]![RequiresState]<>0,DLookUp("StateRecID","tblCity","[CountryRecID]=" & [tblCountryInteractionInfo]![CountryRecID]),"")
Dale FyeOwner, Developing Solutions LLCCommented:
"In almost all cases (and this is one), DLookup() can be replaced by a Left Join to your lookup table."

The exception that I have found, is that this technique may make your recordset non-updateable.  I am currently working on an article that addresses this DLOOKUP speed issue, I'm not sure whether you can see it yet or not, but here is the hyplerlink to it, just in case.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Dale FyeOwner, Developing Solutions LLCCommented:
BTW, the problem with the DLOOKUP was your syntax in the criteria argument.  This argument requires a string, so you must compose that with quotes at the beginning and end.  But in this case, want the value of [tblCountryInteractionInfo]![CountryRecID] to be evaluated before the lookup is executed, so the reference to that table/field must be placed outside of the quotes.
I've run into loss of updateablilty with joining to totals queries but left joins do not cause the problem in and of themselves.

Dale jogged some brain cells loose.  Is StateRecID the FK to the actual state name in a different table?  If you have a table level lookup, you may not even know that StateRecID is actually numeric and not the state name.  If that is the case, you need to join to the table that contains the state name to get the text value.  If you are using a table level lookup, get rid of it now.  Do not pass Go.  That "feature" is simply a crutch for people who don't understand how to create a query with a join.  Once you get past the stage of using Access like a big spreadsheet, table level lookups cause nothing but trouble and you've found it with this need.
Dale FyeOwner, Developing Solutions LLCCommented:
good point, Pat, but I think the problem was probably the way the criteria argument in the original DLOOKUP was formatted.
Karen SchaeferBI ANALYSTAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.