Multiple IIFs

[aftabn10]

Hi, I am trying to write a query with 2 IIF arguments but it doesnt work. I have managed to get the first argument to work which will check to see if Europe = 0 and UK = 0 then this will put the value "Neither" (I put the and between 0 and [UK] to make it work).

i have put the second IIF statement if the first argument = false but it doesnt seem to recognise it. Could somebody please help or guide me as to where I have gone wrong.

Answer: IIf(AND[Europe]>0,[UK]>0,"Neither",(IIF([Europe]=0,"UK", "Europe")))

Thanks in advance.

 

[SOS]

How about this:

Answer: Switch([Europe]=0,"UK",[UK]=0,"Europe",[Europe]>0 And [UK]>0,"Neither")

 

[aftabn10]

SOS, Thanks for your quick response.

I have tried that query, but this gives me a "compile error, in query expression 'Switch([Europe]=0,"UK",[UK]=0,"Europe",[Europe]>0 And [UK]>0,"Neither")

Any ideas why?

 

[SOS]

So are the [Europe] and [UK] fields YES/NO Fields?

 

[aftabn10]

No, there have numbers in the fields as im trying add an extra column that will identify if they belong either one of the areas or both.

 

[SOS]

Well, you can try this one:

Answer: IIf([UK]=0 AND [Europe]>0,"Neither",IIF([Europe]=0,"UK", "Europe"))

 

[aftabn10]

SOS, that didnt work, but i have managed to change it to:

Expr1: 
IIf([UK]>0 And [Europe]>0,"Neither",IIf([Europe]=0,"UK","Europe"))

but it seems to fail on the IIf([Europe]=0 as the 0 values are actually BLANKS.

Would that make a difference?

 

[SOS]

Yes, use the NZ function to change to zeroes.

IIf(Nz([Europe],0)>0 And Nz([UK],0)>0 ...etc.

 

[MSAccessRookie]

Hi, I am trying to write a query with 2 IIF arguments but it doesnt work. I have managed to get the first argument to work which will check to see if Europe = 0 and UK = 0 then this will put the value "Neither" (I put the and between 0 and [UK] to make it work).

i have put the second IIF statement if the first argument = false but it doesnt seem to recognise it. Could somebody please help or guide me as to where I have gone wrong.

Answer: IIf(AND[Europe]>0,[UK]>0,"Neither",(IIF([Europe]=0,"UK", "Europe")))

Thanks in advance.

At LEAST the format of the AND statement is incorrect. It is possible that the lack of grouping in the first expression in the first IIf() statement may also be an issue. Try the following:

IIf( ( [Europe]>0 AND [UK]>0 ), "Neither", ( IIF([Europe]=0,"UK", "Europe") ) )

 

[aftabn10]

SOS, MSAccessRookie, I have tried both but they still dont get to the second IIF Argument as with the Formula that MSAccessRookie added it puts every1 as Europe even if it has figures in the UK Column? Where is this going wrong?

Thanks once again btw...

 

[SOS]

Can you post a copy of your database so we can see it? I think it would be faster.

 

[Brianwarnock]

Why all the brackets
IIf([Europe]>0 AND [UK]>0 , "Neither", IIF([Europe]=0,"UK", "Europe") )

Brian

 

[SOS]

In the function like that, if you don't include the brackets then it will error out. I've found that those are needed so that Access knows it is a field you want and not explicit text. Also, it would appear that we might have a problem because the fields are named the same as the text that is wanting to be returned.

 

[gemma-the-husky]

try this


IIf(nz([Europe],0)=0 and nz([UK],0)=0,"Neither",IIF(nz([Europe],0)>0,"Europe", "UK")))

note that this doesnt allow for both Europe AND UK being non-zero
Also I havent counted the brackets, so they may not match at the end.

 

[Rich]

Isn't the UK in Europe anyway, it was the last time I looked?

 

[SOS]

Isn't the UK in Europe anyway, it was the last time I looked?

Oh, I thought they were part of Iceland

 

[Rich]

Oh, I thought they were part of Iceland

Might as well be, they're both bankrupt

 

[Brianwarnock]

In the function like that, if you don't include the brackets then it will error out. I've found that those are needed so that Access knows it is a field you want and not explicit text. Also, it would appear that we might have a problem because the fields are named the same as the text that is wanting to be returned.

I was talking about () type not [] although it will add those as a field of that name exists.
I ran a simple test just to be sure, my example worked no problem tho' I had no blanks but Dave has shown the use of NZ.

Brian

 

[SOS]

I ran a simple test just to be sure, my example worked no problem

And that's why I think an upload might help. I can't see why it wouldn't work either.

 

[gemma-the-husky]

americans wouldn't understand European Geography