Calculating Multiple IIf Statements 1

[awl]

I am working in Access 97. I have a LookUp field in a table; the choices: Yes, No, Undecided. I want to query each of these into 3 separate fields to transport to a new database. A fourth field I want queried, if they left it blank since this is not a required field in the table. I have set up the first three fields in the query: A. V-Y: IIf([WR356 Input DB]![VMAP (Yes, No, Undecided)]="Yes","1","0&quot B. V-N: IIf([WR356 Input DB]![VMAP (Yes, No, Undecided)]="No","1","0&quot C. V-UND: IIf([WR356 Input DB]![VMAP (Yes, No, Undecided)]="Undecided","1","0&quot. These first three give me the correct entries; either a “1” or “0” when I run the query. The 4th column I want either a “1” or a “0” displayed. Now, I am having problems in setting up the last field. My formula is:

V-UNC: IIf([V-Y]="0",IIf([V-N]="0",IIf([V-UND]="0","1","&quot))

When I run the query, parameter value window prompts input for each of the three fields: V-Y, V-N, V-UND and then displays the data when the query opens. The results are correct: if all three fields have a “0”, a “1” is displayed in the 4th column. However, for all the other records the 4th column is blank. How can I get a “0” to be displayed?

In another trial, I have altered the formula above by inserting a “0” in the formula: V-UNC: IIf([V-Y]="0",IIf([V-N]="0",IIf([V-UND]="0","1","0”))); the following results displayed: Where there are “0”s in each of the 3 fields, a “1” is displayed in the V-UNC field; this is what I want. However, there are only “0”s in the V-UNC column where there is a “1” in the V-UND field, and for the other records, the V-UNC is blank where V-Y and V-N fields have a “1”.

Reference the parameter prompts: in another trial, in my Expression Builder, I have included the name of the query, ie [qryConvert New to Old Table 356/3886]![V-Y] for each of the 3 fields; yet when the query is ran, the parameter value window continues for prompts input for each of the three fields. Question: 1) how can I get around from the 3 separate prompts, 2) why am I getting “0”s in the V-UNC if there is a “1” in the V-UND; and 3) why am I receiving blanks in the V-UNC where there is a “1” in either of the V-Y and V-N fields. Thank you to whomever can lend a suggestion….. Curtis….

 

[JoeMiller]

Whoa! Lot of confusing stuff in there! I think what you are trying to say is if any of the first three columns has a 1, you want to show a 1 in the fourth column, or a 0 if none have a 1. You could use a single iif statement and addition to accomplish this:

V-UNC: IIf([V-Y]+[V-N]+[V-UND]>0,"1","0&quot

Also, you may want to investigate the SWITCH() function which allows you to basically do a SELECT CASE statement.

Switch([MyField]="Yes",1,[MyField]="No" or [MyField]="Undecided",0)

It's a little easier to use than nested IIf statements.

HTH Joe Miller
joe.miller@flotech.net

 

[awl]

Thank you Mr. Joe Miller for the suggestions. The result is what I want. However, I had to replace the “>” with the “=” in the iif statement. Though, I still have a problem when running the query. It prompts input at “each” of the V-Y, V-N and V-UND “Enter Parameter Value” dialog boxes. When I press OK at each of the prompts, the query results are displayed. The query seems to be looking for something that is incorrectly named.

I tried the SWITCH() function formula, the results just gave me blank entries. I am satisfied with the iif statement, except for the “prompts” input. Thank you. Curtis…