Access IIF & AND query

Posted on 2007-10-16
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
    LVL 61

    Expert Comment

    Drop the comma:

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

    Accepted Solution

    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

    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??
    LVL 61

    Expert Comment

    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

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

    Expert Comment

    Glad to help :-)

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Run Application within an Access Macro 2 27
    Greater Than Current Month 2 26
    Create exported XLS from Query 19 13
    System Analysis 5 10
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    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…
    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…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now