Multiple iif statements

[LB79]

Hello,

Ive tried to find the answer to this myself but am not having much luck.
I want to put a staement in a query that has multiple iif.
This is basically what I need:

iif([One]="",[Two],iif[One]>[Two],[One],[Two])

How can i make this work?

Thanks

 

[twoplustwo]

DataSetName: IIf([DataSetID]=1,"Sample",IIf([DataSetID]=2,"300","Both"))

I mean I use the above for a nested IIF statement in a db. I think this is what you want...

 

[KenHigg]

iif(([One] = "") or ([One]<=[Two]),[Two],[One])

???

 

[Brianwarnock]

LB's formula just appears to have () problems

iif([One]="",[Two],iif([One]>[Two],[One],[Two]))

Brian

 

[LB79]

Thank you!
I wonder if you can help me a bit further with this now...
Ive added some moe to it, but the result I gt is #Error.

Field: IIf([ONE]="",[Two],IIf([Two]="",[ONE],IIf([ONE]>[Two],[ONE],[Two])))

What I have is 2 fields in the query with formulas to count days. If the first fields value is blank or lower then the second field, then I want to show the second field result, and vice versa, if the second fields value is blank or lower the the first, then I want to show the first field result.

Thanks

 

[KenHigg]

Seems simple logic dictates that if you only have two possible outcomes you need only one IIf(), three outcomes, two IIf()'s. Or am I missing something?

 

[LB79]

Well... I would agree but... the result isnt quite working like that.
The result should be either ONE or TWO, but in the table data, some of the cells are blank in both fields, which seems to be throwing a spanner in the works.

 

[Brianwarnock]

You haven't said what you want if both have data or both are empty,
tryas base
show: IIf(IsNull([one]) And IsNull([two]),"?",IIf(IsNull([one]) And Not IsNull([two]),[two],[one]))

Brian

On rereading the thread I see that you said if both fields have data then if one<two show two.

This is quite a complex issue for nested IIFs, I would write a function.

 

[MSAccessRookie]

[FONT=&quot]I broke the Statement down as follows and found nothing wrong with the structure. I have in the past, however, encountered issues with the [ONE]="" comparison when I used non-text data types. I have has more consistent success with either IsNull([ONE]) or ([ONE] IS Null). You might want to try one of those to see if it makes a difference.

[/FONT][FONT=&quot]....IIf[/FONT]
[FONT=&quot]....([/FONT]
[FONT=&quot]P1....[ONE]="",[/FONT]
[FONT=&quot]P2....[Two],[/FONT]
[FONT=&quot]P3....IIf[/FONT]
[FONT=&quot]......([/FONT]
[FONT=&quot]..P1....[Two]="",[/FONT]
[FONT=&quot]..P2....[ONE],[/FONT]
[FONT=&quot]..P3....IIf[/FONT]
[FONT=&quot]........([/FONT]
[FONT=&quot]....P1....[ONE]>[Two],[/FONT]
[FONT=&quot]....P2....[ONE],[/FONT]
[FONT=&quot]....P3....[Two][/FONT]
[FONT=&quot]........)[/FONT]
[FONT=&quot]......)[/FONT]
[FONT=&quot]....)[/FONT]

 

[KenHigg]

iif((nz([One]) = 0) or (nz([One])<=nz([Two])),nz([Two]),nz([One]))

???

 

[Brianwarnock]

Ok

show: IIf(IsNull([one]) And IsNull([two]),"?",IIf(([one])<([two]),[two],IIf(IsNull([one]),[two],[one])))

Brian

 

[MSAccessRookie]

I think Ken's approach is exactly what he is looking for

 

[LB79]

It is! Ive changed it slightly but the Nulls are working a treat!
Thanks guys!

 

[Brianwarnock]

It is! Ive changed it slightly but the Nulls are working a treat!
Thanks guys!

Erm Ken didn't use Nulls but his approach is real neat if the fields are numeric, except that I think you may have to put,0 in the Nz ie nz([one],0) etc

Brian

 

[KenHigg]

I assumed since he did a '>' thing they were - ?

 

[MSAccessRookie]

What does it look like now?

 

[Brianwarnock]

I assumed since he did a '>' thing they were - ?

Not an unreasonable assumption

Brian

 

[KenHigg]

And I thought if nz evaluated a numeric field it defaulted to '0', vs if it evaluated a text it defaulted with a zero length string - ?

 

[Brianwarnock]

Not in a query, it is function that behaves differently in a query to VBA, it defaults to a zero length string in a query.

Brian

ps geuss whose just checked help

 

[KenHigg]

...
ps geuss whose just checked help

.
.
.
Me