[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access IIF & AND query

Posted on 2007-10-16
Medium Priority
Last Modified: 2008-01-09
Hi Guys,

I have an access 2007 database which I am running a query that looks at 2 columns within 2 different tables and returns a result based on what it finds. What I've been trying to do is run a query that looks like...

expr1: IIf(([table1].[field1])="0", and (table2].[field2])>0,"d","")

So the idea being...if table1, field1 =0 and table2, field2 = more than 0 display "d" otherwise display nothing.

Is it just that my query is formatted incorrectly or is there a better way to do this?
Question by:fyreheart
  • 4
  • 2
LVL 61

Expert Comment

ID: 20089861
Drop the comma:

IIf(([table1].[field1])=0 and (table2].[field2])>0,"d","")
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 20089873
also drop the quotes if field 1 is numeric.

Field1 Numeric:
expr1: IIf(([table1].[field1])=0 and (table2].[field2])>0,"d","")

Field1 text:
expr1: IIf(([table1].[field1])="0" and (table2].[field2])>0,"d","")

Author Comment

ID: 20089922
Thanks for the quick response...the field is numeric so I've entered the following..

expr1: IIf(([table1].[field1])=0 And ([table2].[field2])>0,"d","")

now I get a box asking me to enter a parameter for table2, field2??
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 61

Expert Comment

ID: 20089934
That means that you do not have a field named Field2 in your query.

Right click on your query's design, select SQL view, and paste the SQL from the editor in your next comment here....

Author Comment

ID: 20089935
Doh, my fault...I'd made a mistake in the relationships :) Thanks for your assistance!
LVL 61

Expert Comment

ID: 20089937
Glad to help :-)

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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