multiple conditions criteria iif?

[Starry]

Hi

I cannot figure out how to test multiple conditions in a single grid field
as follows

Grp: IIf(IsNull([field1]),0,1) else IIf(IsNull([field2]),0,2) etc. etc.

two is straight forward enough with iif but I need to test four fields and
place 1,2,3 or 4 in Grp

My searches have drawn a blank so please refer if I have missed a post.
Thanks.

 

[Dale Fye]

Not exzctly sure what you are trying to do here. But I'm going to assume
that what you are trying to do is identify the field that contains the first
non-null value.

Grp: IIF(NOT ISNULL([Field1]), 1, IIF(NOT ISNULL([Field2]), 2, 0))

If what you actually want is the value of that field, rather than an index
to the field, it would look something like:

Grp: IIF(NOT ISNULL([Field1]), [Field1], IIF(NOT ISNULL([Field2]), [Field2],
0))

HTH
Dale

 

[John Spencer]

Don't really know what you are attempting to do from your description, but
the following will put in a value of 1,2,3,4, or 0, based on the first field
it finds that is null . Zero if none of the fields is null.

Grp: SWITCH(IsNull([Field1]),1, IsNull([Field2]),2,
IsNull([Field3]),3,IsNull([Field4]),4,True,0)

You can also do this with nested IIF statements (up to seven levels allowed)
IIF(IsNull(Field1),1,
IIF(IsNull(Field2),1,IIF(IsNull(field3),3,IIF(IsNull(Field4),4,0))))

Hopefully I got all the parentheses matched up correctly in the above.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Starry]

Thanks for the response Dale I need more than one condition though.
John you hit the nail on the head....but....also made me realise that I
posed the question wrongly

I need Grp to equal 123or4 depending on which field HAS data so my logic for
using IsNull to work this backwards is wack! It doesn't matter how it's
achieved really I wish that Is Not Null was a simiar function but I don't
think I can use it in this senario?

Any ideas (I'll keep the other for the future, i've needed that before!)

Thanks

Don't really know what you are attempting to do from your description, but
the following will put in a value of 1,2,3,4, or 0, based on the first
field it finds that is null . Zero if none of the fields is null.

Grp: SWITCH(IsNull([Field1]),1, IsNull([Field2]),2,
IsNull([Field3]),3,IsNull([Field4]),4,True,0)

You can also do this with nested IIF statements (up to seven levels
allowed)
IIF(IsNull(Field1),1,
IIF(IsNull(Field2),1,IIF(IsNull(field3),3,IIF(IsNull(Field4),4,0))))

Hopefully I got all the parentheses matched up correctly in the above.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi

I cannot figure out how to test multiple conditions in a single grid
field as follows

Grp: IIf(IsNull([field1]),0,1) else IIf(IsNull([field2]),0,2) etc. etc.

two is straight forward enough with iif but I need to test four fields
and place 1,2,3 or 4 in Grp

My searches have drawn a blank so please refer if I have missed a post.
Thanks.

 

[John Spencer]

Since you are doing this in a query, you can use

Grp: Switch([Field1] Is Not Null,1, [Field2] Is Not Null, 2, [Field3] Is Not
Null, 3, [Field4] is Not Null, 4)

Or

IIF(IsNull([Field1])=False,1, IIF(IsNull(Field2)=False, 2, IIF(...))))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the response Dale I need more than one condition though.
John you hit the nail on the head....but....also made me realise that I
posed the question wrongly

I need Grp to equal 123or4 depending on which field HAS data so my logic
for using IsNull to work this backwards is wack! It doesn't matter how
it's achieved really I wish that Is Not Null was a simiar function but I
don't think I can use it in this senario?

Any ideas (I'll keep the other for the future, i've needed that before!)

Thanks

Don't really know what you are attempting to do from your description,
but the following will put in a value of 1,2,3,4, or 0, based on the
first field it finds that is null . Zero if none of the fields is null.

Grp: SWITCH(IsNull([Field1]),1, IsNull([Field2]),2,
IsNull([Field3]),3,IsNull([Field4]),4,True,0)

You can also do this with nested IIF statements (up to seven levels
allowed)
IIF(IsNull(Field1),1,
IIF(IsNull(Field2),1,IIF(IsNull(field3),3,IIF(IsNull(Field4),4,0))))

Hopefully I got all the parentheses matched up correctly in the above.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi

I cannot figure out how to test multiple conditions in a single grid
field as follows

Grp: IIf(IsNull([field1]),0,1) else IIf(IsNull([field2]),0,2) etc. etc.

two is straight forward enough with iif but I need to test four fields
and place 1,2,3 or 4 in Grp

My searches have drawn a blank so please refer if I have missed a post.
Thanks.

 

[Starry]

Many many thanks John perfect.

Now I'll go figure what Switch is all about!?

Since you are doing this in a query, you can use

Grp: Switch([Field1] Is Not Null,1, [Field2] Is Not Null, 2, [Field3] Is
Not Null, 3, [Field4] is Not Null, 4)

Or

IIF(IsNull([Field1])=False,1, IIF(IsNull(Field2)=False, 2, IIF(...))))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Thanks for the response Dale I need more than one condition though.
John you hit the nail on the head....but....also made me realise that I
posed the question wrongly

I need Grp to equal 123or4 depending on which field HAS data so my logic
for using IsNull to work this backwards is wack! It doesn't matter how
it's achieved really I wish that Is Not Null was a simiar function but I
don't think I can use it in this senario?

Any ideas (I'll keep the other for the future, i've needed that before!)

Thanks

Don't really know what you are attempting to do from your description,
but the following will put in a value of 1,2,3,4, or 0, based on the
first field it finds that is null . Zero if none of the fields is null.

Grp: SWITCH(IsNull([Field1]),1, IsNull([Field2]),2,
IsNull([Field3]),3,IsNull([Field4]),4,True,0)

You can also do this with nested IIF statements (up to seven levels
allowed)
IIF(IsNull(Field1),1,
IIF(IsNull(Field2),1,IIF(IsNull(field3),3,IIF(IsNull(Field4),4,0))))

Hopefully I got all the parentheses matched up correctly in the above.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"Starry" <nospam> wrote in message
Hi

I cannot figure out how to test multiple conditions in a single grid
field as follows

Grp: IIf(IsNull([field1]),0,1) else IIf(IsNull([field2]),0,2) etc. etc.

two is straight forward enough with iif but I need to test four fields
and place 1,2,3 or 4 in Grp

My searches have drawn a blank so please refer if I have missed a post.
Thanks.