MS Access IIF instead of LEFT JOIN

Posted on 2011-10-03
Medium Priority
Last Modified: 2013-11-27

I've been working on a fairly large SQL query in Access 2007.  I've run into a snag using LEFT JOINs for my lookup tables.  More specifically, I've reached the allowable number of JOINS in Access.  

I'd like to try to use an IIF statement to try to workaround it but I'm not sure exactly how to do it correctly.  My goal is to replace my LEFT JOINS for Yes/No values with an IIF statement so I can free up some JOINS for other lookup fields.  

In an attempt to make this work, I placed the following in the "Field:" row of the column in which I'd like Yes or No returned:

MyField: IIF([MyTable].[YesNo1]="2","Yes","No")

This runs but in the returned data I get "#ERROR".  Is this possible?  If so, do you know where I've gone wrong?

Question by:ttist25
LVL 58
ID: 36904995
<<This runs but in the returned data I get "#ERROR".  Is this possible?  If so, do you know where I've gone wrong?>>

  Your syntax is correct, but you most likely have a broken reference.  Open a code module in design view, click tools/references and look for any that are MISSING or BROKEN.

  If none are, check any unchecked reference, then close the DB and Access, re-open and uncheck the reference just checked.  Then compile and save.

 Then try the query.

LVL 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 36905134
Or it could be that the field is numeric, not text:

instead of:

MyField: IIF([MyTable].[YesNo1]="2","Yes","No")


MyField: IIF([MyTable].[YesNo1]=2,"Yes","No")


Author Closing Comment

ID: 36905203
That's what it was.  I figured it out as you were posting it!  Thanks!

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 49

Expert Comment

by:Dale Fye
ID: 36905277
So, what is the limit on the number of joins?

I've written some pretty outrageous queries, but don't think I've ever run into that problem.
LVL 26

Expert Comment

ID: 36905298
You can create queries of queries.
It might be much more mainatainable to grab some of your data, save it as a query, and then use that query in another query.
Queries behave just like tables.

A huge query with a bazillion IIF([MyTable].[YesNo1]="2","Yes","No")
will be difficult to maintain
LVL 35

Expert Comment

ID: 36905733
The maximum no of joins in a query is 16.

There must be some other way to do this rather than using a whole bunch of Iifs and what sounds like a lot of joins.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question