Nested IIF AND statements

  • Thread starter Thread starter dl
  • Start date Start date
D

dl

I've read through the other posts on this subject and can't seem to apply any
of that to solve my problem. I'm hoping someone can tell me where I'm going
wrong.

What I'm trying to accomplish is this: A vendor sends in a sync file
reflecting the discount codes on items we buy -we then compare our values
against theirs to find any that have changed, and then update our systems.
No problem. My current iif statement worked fine. (note we translate some
of their discount codes to another code for our system, ie Discount Code
could be changed to NEW DISCOUNT CODE via a cross ref table)

The old, working iif statement:

IIf([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is Null,[SUPPLIED
DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE])

....which of course is saying: If the NEW DISCOUNT CODE field in the cross
reference table is null, then use the original discount code that was
submitted, otherwise use the NEW DISCOUNT CODE.

cross ref table looks like this:

Discount Code NEW DISCOUNT CODE Partnership Code
A08 null null
A52 null null
.... ... ...
AA2 TRADE2 null
.... ... ...
A98 TRADE TRBL2%

Now, we're entering a partnership where we're allowed to reassign the
discount code on items where the publication date is >=13 months from today,
and are currently assigned to a set of eligible discount codes (those that
show TRBL2% in the Partnership column of the xRef table).

So I need to nest the previous IIF with a new one that does date math and
here's where I am:

IIf(([BORMS01_UDA_ITEM_DATE]![UDA_DATE]>=dateadd('m',-13,now()),AND([SUPPLIED
DISCOUNTS - DK]![Partnership Code]=†TRBL2%â€),[SUPPLIED DISCOUNTS -
DK]![Partnership Code],IIf(([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is
Null,[SUPPLIED DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS - DK]![NEW
DISCOUNT CODE])

Please let me know if you need additional info, and THANK YOU!!!!
dl
 
I don't know if this applies to your problem, but this expression is
incorrect..

IIf([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is Null ...

You can't test for a null value that way. You need to use the IsNull
Function like,
 
I've come up with this now:

IIf([BORMS01_UDA_ITEM_DATE]![UDA_DATE]>=DateAdd('m',-13,Now() And [SUPPLIED
DISCOUNTS - DK]![Partnership Code]="TRBL2%"),[SUPPLIED DISCOUNTS -
DK]![Partnership Code],IIf([SUPPLIED DISCOUNTS - DK]![Partnership Code] Is
Null,[SUPPLIED DISCOUNTS - DK]![Discount Code]))

....which Access seems to have accepted. Can anyone see a problem with this?
(Testing this will take a lonnnng time if I let it run, so I thought I'd see
if anyone here can see a blatant error before I try it.)

Thanks again!

dl said:
I've read through the other posts on this subject and can't seem to apply any
of that to solve my problem. I'm hoping someone can tell me where I'm going
wrong.

What I'm trying to accomplish is this: A vendor sends in a sync file
reflecting the discount codes on items we buy -we then compare our values
against theirs to find any that have changed, and then update our systems.
No problem. My current iif statement worked fine. (note we translate some
of their discount codes to another code for our system, ie Discount Code
could be changed to NEW DISCOUNT CODE via a cross ref table)

The old, working iif statement:

IIf([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is Null,[SUPPLIED
DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE])

...which of course is saying: If the NEW DISCOUNT CODE field in the cross
reference table is null, then use the original discount code that was
submitted, otherwise use the NEW DISCOUNT CODE.

cross ref table looks like this:

Discount Code NEW DISCOUNT CODE Partnership Code
A08 null null
A52 null null
... ... ...
AA2 TRADE2 null
... ... ...
A98 TRADE TRBL2%

Now, we're entering a partnership where we're allowed to reassign the
discount code on items where the publication date is >=13 months from today,
and are currently assigned to a set of eligible discount codes (those that
show TRBL2% in the Partnership column of the xRef table).

So I need to nest the previous IIF with a new one that does date math and
here's where I am:

IIf(([BORMS01_UDA_ITEM_DATE]![UDA_DATE]>=dateadd('m',-13,now()),AND([SUPPLIED
DISCOUNTS - DK]![Partnership Code]=†TRBL2%â€),[SUPPLIED DISCOUNTS -
DK]![Partnership Code],IIf(([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is
Null,[SUPPLIED DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS - DK]![NEW
DISCOUNT CODE])

Please let me know if you need additional info, and THANK YOU!!!!
dl
Click to expand...
 
Yes, you aren't using the Isnull function properly.

IIf([SUPPLIED DISCOUNTS - DK]![Partnership Code] Is
Null

should be

IIf(IsNull([SUPPLIED DISCOUNTS - DK]![Partnership Code])

The syntax is

IsNull(<expression>)

God Bless,

Mark A. Sam


dl said:
I've come up with this now:

IIf([BORMS01_UDA_ITEM_DATE]![UDA_DATE]>=DateAdd('m',-13,Now() And
[SUPPLIED
DISCOUNTS - DK]![Partnership Code]="TRBL2%"),[SUPPLIED DISCOUNTS -
DK]![Partnership Code],IIf([SUPPLIED DISCOUNTS - DK]![Partnership Code] Is
Null,[SUPPLIED DISCOUNTS - DK]![Discount Code]))

...which Access seems to have accepted. Can anyone see a problem with
this?
(Testing this will take a lonnnng time if I let it run, so I thought I'd
see
if anyone here can see a blatant error before I try it.)

Thanks again!

dl said:
I've read through the other posts on this subject and can't seem to apply
any
of that to solve my problem. I'm hoping someone can tell me where I'm
going
wrong.

What I'm trying to accomplish is this: A vendor sends in a sync file
reflecting the discount codes on items we buy -we then compare our values
against theirs to find any that have changed, and then update our
systems.
No problem. My current iif statement worked fine. (note we translate
some
of their discount codes to another code for our system, ie Discount Code
could be changed to NEW DISCOUNT CODE via a cross ref table)

The old, working iif statement:

IIf([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is Null,[SUPPLIED
DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT
CODE])

...which of course is saying: If the NEW DISCOUNT CODE field in the cross
reference table is null, then use the original discount code that was
submitted, otherwise use the NEW DISCOUNT CODE.

cross ref table looks like this:

Discount Code NEW DISCOUNT CODE Partnership Code
A08 null null
A52 null null
... ...
...
AA2 TRADE2 null
... ...
...
A98 TRADE TRBL2%

Now, we're entering a partnership where we're allowed to reassign the
discount code on items where the publication date is >=13 months from
today,
and are currently assigned to a set of eligible discount codes (those
that
show TRBL2% in the Partnership column of the xRef table).

So I need to nest the previous IIF with a new one that does date math and
here's where I am:

IIf(([BORMS01_UDA_ITEM_DATE]![UDA_DATE]>=dateadd('m',-13,now()),AND([SUPPLIED
DISCOUNTS - DK]![Partnership Code]=" TRBL2%"),[SUPPLIED DISCOUNTS -
DK]![Partnership Code],IIf(([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE]
Is
Null,[SUPPLIED DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS -
DK]![NEW
DISCOUNT CODE])

Please let me know if you need additional info, and THANK YOU!!!!
dl
Click to expand...
Click to expand...
 
I figured it out, and it works beautifully. Just wanted to post it in case
someone can use it someday.

The working version:
IIf([PUB DATE: US]<=DateAdd('m',-13,Now()) And ([Partnership Code] Is Not
Null),[Partnership Code],IIf([NEW DISCOUNT CODE] Is Null,[SUPPLIED DISCOUNTS
- DK]![Discount Code],[NEW DISCOUNT CODE]))

In English: IIf "Pub Date" is before or equal to 13 months ago compared to
today and the "Partnership Code" field in the Code reference table Is Not
Null, then use the "Partnership Code", otherwise IIf the "New Discount Code"
field in the Code reference table Is Null use the value in the "Discount
Code" field from the "Supplied Discounts -DK" table, otherwise use the value
in the "New Discount Code" field from that table.

My problem seemed to be the fact that I didn't have a "(" after the AND
statement, and needed to include an else for the second iif.

Hope that helps somebody.

dl said:
I've come up with this now:

IIf([BORMS01_UDA_ITEM_DATE]![UDA_DATE]>=DateAdd('m',-13,Now() And [SUPPLIED
DISCOUNTS - DK]![Partnership Code]="TRBL2%"),[SUPPLIED DISCOUNTS -
DK]![Partnership Code],IIf([SUPPLIED DISCOUNTS - DK]![Partnership Code] Is
Null,[SUPPLIED DISCOUNTS - DK]![Discount Code]))

...which Access seems to have accepted. Can anyone see a problem with this?
(Testing this will take a lonnnng time if I let it run, so I thought I'd see
if anyone here can see a blatant error before I try it.)

Thanks again!

dl said:
I've read through the other posts on this subject and can't seem to apply any
of that to solve my problem. I'm hoping someone can tell me where I'm going
wrong.

What I'm trying to accomplish is this: A vendor sends in a sync file
reflecting the discount codes on items we buy -we then compare our values
against theirs to find any that have changed, and then update our systems.
No problem. My current iif statement worked fine. (note we translate some
of their discount codes to another code for our system, ie Discount Code
could be changed to NEW DISCOUNT CODE via a cross ref table)

The old, working iif statement:

IIf([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is Null,[SUPPLIED
DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE])

...which of course is saying: If the NEW DISCOUNT CODE field in the cross
reference table is null, then use the original discount code that was
submitted, otherwise use the NEW DISCOUNT CODE.

cross ref table looks like this:

Discount Code NEW DISCOUNT CODE Partnership Code
A08 null null
A52 null null
... ... ...
AA2 TRADE2 null
... ... ...
A98 TRADE TRBL2%

Now, we're entering a partnership where we're allowed to reassign the
discount code on items where the publication date is >=13 months from today,
and are currently assigned to a set of eligible discount codes (those that
show TRBL2% in the Partnership column of the xRef table).

So I need to nest the previous IIF with a new one that does date math and
here's where I am:

IIf(([BORMS01_UDA_ITEM_DATE]![UDA_DATE]>=dateadd('m',-13,now()),AND([SUPPLIED
DISCOUNTS - DK]![Partnership Code]=†TRBL2%â€),[SUPPLIED DISCOUNTS -
DK]![Partnership Code],IIf(([SUPPLIED DISCOUNTS - DK]![NEW DISCOUNT CODE] Is
Null,[SUPPLIED DISCOUNTS - DK]![Discount code],[SUPPLIED DISCOUNTS - DK]![NEW
DISCOUNT CODE])

Please let me know if you need additional info, and THANK YOU!!!!
dl
Click to expand...
Click to expand...
 
Back
Top