You are here:

Using MS Access/access 2007 iif statement

Advertisement


Question
QUESTION: Hi,  I'm new to Access.  Trying to use an iif function in a report.  There are 3 fields with vales of "yes" "no" "n/a" or "blank".  I would like to have a field in a report ask if all 3 of the fields are "no" then return no.  If any of the fields are yes then return yes.  

In the report in a text box I have:   =iif([field_1]="no" and [field_2]="no" and [field_3]="no","no","yes")  

it returns only yes.

Thank you for your help.

ANSWER: Hmm that should work. Are you sure the field holds text values?

I would, however, do this differently. I would use a Number>Integer datatype for the field, and use a combobox to select the value. The relevant properties of the combo are:

RowSource: SELECT RecordID, Description FROM tblYesNo;
Bound Column: 1
Column Count: 2
Column Widths: 0";.5"

You would then create a table like so:

tblYesNo
RecordID (PK)
Description

ID Description
0  No
1  Yes
2  N/A
3  Blank

Then your IIF could be:

=IIF([Field1+Field2+Field3=0,"No","Yes")

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: the fields with yes,no,n/a are from a table tblyesno, the Data Type for the column is set to Text.  The yes,no,n/a is a comboBox on a form.. then the report pulls the information from a Query.  When the iif function is used in the report,  iif([field_1]="no" and [field_2]="no" and [field_3]="no","no","yes")    it only returns with the yes. I'm not sure if the combobox or the query is affecting how the function is working??   I also tried using the iif functin in the query and on the form, but nothing is working the way it should.

Thank you helping.

ANSWER: Does the Table have only one column? What are the Data types for Field1, 2 & 3?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: the data types were number I changed to data, but still got the same output as only getting false.

Finally figured it out.  I guess because it was coming from a combobox it was reading the "yes" as 1 the "no" as 2 and the"n/a" as 3 and blank as blank.. so i just changed the = "no" to  = 2 and so forth..and it worked!   thank you again.. your suggestion would have been the best why to set it up.

Answer
That's what I was trying to get at. I suspect you set up a Lookup field on the table level. If you did, it is not recommended because it masks what is actually being stored and causes such problems.

However, you can still use the expression I gave you. Since No is 2, then if all 3 are No, the total would equal 6. So you could use:

=IIF([Field1+Field2+Field3=6,"No","Yes")

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.