IIF statement in MSAccess Query...

[Andre Laplume via AccessMonster.com]

I have a data set with 3 columns in a table: Amt, Percent and Type. Any
given row may be categorized by a Type = 1, 2, 3 or 4. I have a defined item:
'AdjAmt'. AdjAmt should be equal to Amt * Percent WHEN the row Type = 2
AND we are in the month of JULY. Otherwise AdjAmt is equal to Amt * Percent
WHEN the row type = 1. Otherwise AdjAmt is zero. So AdjAmt sums the TYPE=2
records if we are in the month of July and sums the TYPE=1 records in other
months. IF the row TYPE is other than 1 or 2 it is ignored.

This definition works:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],0))

This should return the exact same amount since we are indeed in JULY but does
not…Why?

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,
[Amt]*[Percent],0))))


THANKS!

 

[Andre Laplume via AccessMonster.com]

I have a data set with 3 columns in a table: Amt, Percent and Type. Any
given row may be categorized by a Type = 1, 2, 3 or 4. I have a defined item:
'AdjAmt'. AdjAmt should be equal to Amt * Percent WHEN the row Type = 2
AND we are in the month of JULY. Otherwise AdjAmt is equal to Amt * Percent
WHEN the row type = 1. Otherwise AdjAmt is zero. So AdjAmt sums the TYPE=2
records if we are in the month of July and sums the TYPE=1 records in other
months. IF the row TYPE is other than 1 or 2 it is ignored.

This definition works:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],0))

This should return the exact same amount since we are indeed in JULY but does
not…Why?

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,
[Amt]*[Percent],0))))

THANKS!

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Andre Laplume via AccessMonster.com]

NEVERMIND...figured it out...need a NOT July condition on the second if.....

I have a data set with 3 columns in a table: Amt, Percent and Type. Any
given row may be categorized by a Type = 1, 2, 3 or 4. I have a defined item:

[quoted text clipped - 15 lines]

THANKS!

[Amt]*[Percent],0)))

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Chaim]

I couldn't get it to break. the only thing I did different was to enclose the
IIF condition in (...), which are not really needed.

BTW, you're probably better off using something like Month() to get the
month out of a date string. It's not as sensitive to date format. OR, I
should say, it is consistent with the system's idea of a date format as would
be returned from Date().
--
Chaim

I have a data set with 3 columns in a table: Amt, Percent and Type. Any
given row may be categorized by a Type = 1, 2, 3 or 4. I have a defined item:
'AdjAmt'. AdjAmt should be equal to Amt * Percent WHEN the row Type = 2
AND we are in the month of JULY. Otherwise AdjAmt is equal to Amt * Percent
WHEN the row type = 1. Otherwise AdjAmt is zero. So AdjAmt sums the TYPE=2
records if we are in the month of July and sums the TYPE=1 records in other
months. IF the row TYPE is other than 1 or 2 it is ignored.

This definition works:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],0))

This should return the exact same amount since we are indeed in JULY but does
not…Why?

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,
[Amt]*[Percent],0))))

THANKS!

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))