Solved Nested iif? (1 Viewer)

PNEfinney

New member
Local time
Today, 22:58
Joined
Oct 3, 2019
Messages
19
hi, hoping you can all help.

I am possibly being a bit naive in thinking i can transfer an 'if' design from EXCEL into ACCESS;

I essentially want to do waht this does in access:
=IF($BD2="",IF($AW2="",IF($AO2="",IF($AH2="",IF($AD2="","",$AD2),$AH2),$AO2),$AW2),$BD2)

I want access to look at one field, and if blank move to the next one, if it has any wording in it, i want it to return that.

The background is that i am trying to find the end state based on outcome returns from 6 reports. The fields are either blank or have 'Complete', Not Complete'. My end state would be for the right end column to say 'Complete', 'Not Complete' or be blank (tho there shouldnt be any blanks).

My query field list is:

UNIQUE IDENT/ OCT / NOV/ MOPUP / DEC

My thinking is that the query would look at Dec, and return Complete/ Not Complete, if blank, move back to MOPUP and do the same all the way through until when it gets to OCT when if its blank, it will return blank.

I initially just copied the excel idea into an 'iif', but it doesnt seem to work for three way outcomes.

Any ideas?
 

Minty

AWF VIP
Local time
Today, 22:58
Joined
Jul 26, 2013
Messages
10,355
I think you have a bit of a design issue, you should never have fields names that are data.
So when we see fields called OCT or NOV this shouts of a design that is a Excel spreadsheet dumped into Access.
That doesn't work.
Your date data should be part of a record, and then this query would be simple.

Can you describe your data and the desired output from a high level, not database jargon? Or post up some sample data, and we can probably show you how it should be stored.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:58
Joined
Feb 19, 2013
Messages
16,553
if and iif will work the same

really don't understand your explanation - why not just reference the right column?

with databases you also have to contend with nulls. Since your values are text, they will 'look' the same

iif(nz([unique],"")="", .........

if your blanks are in fact nulls, you can use a nested nz function

nz([unique],nz([ident], ......

Also, adapting excel formulas to databases implies your table design is not correct
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:58
Joined
May 7, 2009
Messages
19,169
see suggestion on post #2.
re-structure your table so it increase in "height" and not in "width"
if you are unable to do the re-structuring, you use Switch() function:

Switch(IsNull([Oct])=False, [Oct], IsNull([Nov])=False, [Nov], IsNull([MOPUP])=False, [MOPUP], IsNull([Dec])=False, [Dec], True, "") As Expr1
 

PNEfinney

New member
Local time
Today, 22:58
Joined
Oct 3, 2019
Messages
19
I think you have a bit of a design issue, you should never have fields names that are data.
So when we see fields called OCT or NOV this shouts of a design that is a Excel spreadsheet dumped into Access.
That doesn't work.
Your date data should be part of a record, and then this query would be simple.

Can you describe your data and the desired output from a high level, not database jargon? Or post up some sample data, and we can probably show you how it should be stored.

Thanks for replying.

In explanation i sent reports out to a third party who would update records in an Oracle system. where the update went well, they added a new column 'Status' that would have 'Complete' or if not complete, '*the reason it failed*

These were done on a regular basis, monthly over Oct to Dec, and then a 'MopUp' session and a Corrections session. I pulled all these return reports into ACCESS and Using a union, then append got all the unique identifiers into a maketable then append using primary key to remove Duplicates.
then that report was left joined with all the returns. the field names were ones i designed to make it simpler to identify each month.

So the reason for this query is literally me trying to ensure that every record is complete, or that i need to do some more work.

apologies for the jargon
 

PNEfinney

New member
Local time
Today, 22:58
Joined
Oct 3, 2019
Messages
19
if and iif will work the same

really don't understand your explanation - why not just reference the right column?

with databases you also have to contend with nulls. Since your values are text, they will 'look' the same

iif(nz([unique],"")="", .........

if your blanks are in fact nulls, you can use a nested nz function

nz([unique],nz([ident], ......

Also, adapting excel formulas to databases implies your table design is not correct
i wasnt adapting the formula so much as the intention, if that makes sense. in my initial formula I used 'Is Null', but ill try your suggestion. Thank you.
 

PNEfinney

New member
Local time
Today, 22:58
Joined
Oct 3, 2019
Messages
19
see suggestion on post #2.
re-structure your table so it increase in "height" and not in "width"
if you are unable to do the re-structuring, you use Switch() function:

Switch(IsNull([Oct])=False, [Oct], IsNull([Nov])=False, [Nov], IsNull([MOPUP])=False, [MOPUP], IsNull([Dec])=False, [Dec], True, "") As Expr1
Thank you, id clean forgotten about Switch

Appreciate you posting this, TY

EDIT: This did exactly what i needed to, i just switched the order so Dec was at front of the expression,and it worked backwards. Thank you for all your help.
 
Last edited:

Users who are viewing this thread

Top Bottom