Problem with iif condition in expression

[anyaley via AccessMonster.com]

I am using a calculated field called "Balance" in a query which is the sum of
total credits less total debits for an account.i.e Balance: sum(([cramt]-
[dramt])) and is OK. Now I want to it based on Account Type. i.e
If Acctyp = "D" Balance: sum(([dramt]-[cramt]))
If Actype = "C" Balance: sum(([cramt]-[dramt]))
How can I use one iif expression to do this. I tried this below but it did
not work;

Balance: iif [Acctyp] = "D", sum(([dramt]-[cramt])),sum(([cramt]-[dramt]))


Please help

 

[Michel Walsh]

SUM(dramt-cramt) *iif(acctyp='D', 1, -1)

since the type C is - what we have for type D.


In general, you can use:


iif( acctyp='D', SUM(dramt-cramt), SUM(cramt-dramt) )



You were missing ( after iif, and its closing counter part, at the end.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Are D and C the only two values in Acctyp? If so, you should be able to use:
Balance: Sum(IIf(Acctyp="D",[dramt]-[cramt],[cramt]-[dramt]))

 

[anyaley via AccessMonster.com]

SUM(dramt-cramt) *iif(acctyp='D', 1, -1)

since the type C is - what we have for type D.

In general, you can use:

iif( acctyp='D', SUM(dramt-cramt), SUM(cramt-dramt) )

You were missing ( after iif, and its closing counter part, at the end.

Hoping it may help,
Vanderghast, Access MVP

I am using a calculated field called "Balance" in a query which is the sum
of

[quoted text clipped - 9 lines]

Please help

I have tried your solution and got this message
"you tried to execute query that does not include the specified expression as
part of an aggregate function."

For your info, Acctype is in table called tbmaccts. dramount and tramount are
transaction table called tbtaccts. The access query is like this:

Field Acctno dramount cramount Balanceyour solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression

 

[anyaley via AccessMonster.com]

Are D and C the only two values in Acctyp? If so, you should be able to use:
Balance: Sum(IIf(Acctyp="D",[dramt]-[cramt],[cramt]-[dramt]))

I am using a calculated field called "Balance" in a query which is the sum of
total credits less total debits for an account.i.e Balance: sum(([cramt]-

I have tried your solution and got this message
"you tried to execute query that does not include the specified expression as
part of an aggregate function."

For your info, Acctype is in table called tbmaccts. dramount and tramount are
transaction table called tbtaccts. The access query is like this:

Field Acctno dramount cramount Balanceyour solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression

[quoted text clipped - 7 lines]

Please help

 

[Michel Walsh]

In that case, indeed, we have to include the iif inside the SUM:


SUM( (dramt-cramt) * iif(acctyp='D', 1, -1) )



Hoping it may help,
Vanderghast, Access MVP

SUM(dramt-cramt) *iif(acctyp='D', 1, -1)

since the type C is - what we have for type D.

In general, you can use:

iif( acctyp='D', SUM(dramt-cramt), SUM(cramt-dramt) )

You were missing ( after iif, and its closing counter part, at the end.

Hoping it may help,
Vanderghast, Access MVP

I am using a calculated field called "Balance" in a query which is the
sum
of

[quoted text clipped - 9 lines]

Please help

I have tried your solution and got this message
"you tried to execute query that does not include the specified expression
as
part of an aggregate function."

For your info, Acctype is in table called tbmaccts. dramount and tramount
are
transaction table called tbtaccts. The access query is like this:

Field Acctno dramount cramount Balanceyour solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression

 

[Michel Walsh]

Can you post the SQL statement as it appears in the SQL view?


Vanderghast, Access MVP

Are D and C the only two values in Acctyp? If so, you should be able to
use:
Balance: Sum(IIf(Acctyp="D",[dramt]-[cramt],[cramt]-[dramt]))

I am using a calculated field called "Balance" in a query which is the
sum of
total credits less total debits for an account.i.e Balance:
sum(([cramt]-

I have tried your solution and got this message
"you tried to execute query that does not include the specified expression
as
part of an aggregate function."

For your info, Acctype is in table called tbmaccts. dramount and tramount
are
transaction table called tbtaccts. The access query is like this:

Field Acctno dramount cramount Balanceyour solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression

[quoted text clipped - 7 lines]

Please help

 

[Guest]

Please provide your full SQL view. YOu may be able to remove the Sum() from
my expression and just use the IIf(). Then set the Total row to Sum.
--
Duane Hookom
Microsoft Access MVP

Are D and C the only two values in Acctyp? If so, you should be able to use:
Balance: Sum(IIf(Acctyp="D",[dramt]-[cramt],[cramt]-[dramt]))

I am using a calculated field called "Balance" in a query which is the sum of
total credits less total debits for an account.i.e Balance: sum(([cramt]-

I have tried your solution and got this message
"you tried to execute query that does not include the specified expression as
part of an aggregate function."

For your info, Acctype is in table called tbmaccts. dramount and tramount are
transaction table called tbtaccts. The access query is like this:

Field Acctno dramount cramount Balanceyour solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression

[quoted text clipped - 7 lines]

Please help

 

[anyaley via AccessMonster.com]

Can you post the SQL statement as it appears in the SQL view?

Vanderghast, Access MVP

Are D and C the only two values in Acctyp? If so, you should be able to
use: [quoted text clipped - 20 lines]

Please help

Thanks, the problem is resolved and here is the output below:

Transaction Table
Date DocNum AccNum DrAmount CrAmount Balance Acctype Bal.-Code
27-Feb-07 501 1000 5,000.00 50.00 $4,950.00 D 1
27-Feb-07 501 3000 0.00 5,000.00 $5,000.00 C 3
27-Feb-07 501 4000 50.00 800.00 $750.00 C 4
27-Feb-07 501 1000 500.00 0.00 $500.00 D 1
27-Feb-07 501 1020 300.00 0.00 $300.00 D 1


Solution
AccNum SumOfDrAmount SumOfCrAmount Balance DRCR Code
1000 $5,500.00 $50.00 $5,450.00 D
1020 $300.00 $0.00 $300.00 D
3000 $0.00 ,000.00 $5,000.00 C
4000 $50.00 800.00 $750.00 C

What if I want to use the Bal-code
code 1 or 5 =dramount - cramount
code 2,3 and 4 = cramount-dramount.

 

[anyaley via AccessMonster.com]

Please provide your full SQL view. YOu may be able to remove the Sum() from
my expression and just use the IIf(). Then set the Total row to Sum.

Are D and C the only two values in Acctyp? If so, you should be able to use:
Balance: Sum(IIf(Acctyp="D",[dramt]-[cramt],[cramt]-[dramt])) [quoted text clipped - 15 lines]

Please help

Thanks, the problem is resolved and here is the output below:

Transaction Table
Date DocNum AccNum DrAmount CrAmount Balance Acctype Bal.-Code
27-Feb-07 501 1000 5,000.00 50.00 $4,950.00 D 1
27-Feb-07 501 3000 0.00 5,000.00 $5,000.00 C 3
27-Feb-07 501 4000 50.00 800.00 $750.00 C 4
27-Feb-07 501 1000 500.00 0.00 $500.00 D 1
27-Feb-07 501 1020 300.00 0.00 $300.00 D 1


Solution
AccNum SumOfDrAmount SumOfCrAmount Balance DRCR Code
1000 $5,500.00 $50.00 $5,450.00 D
1020 $300.00 $0.00 $300.00 D
3000 $0.00 ,000.00 $5,000.00 C
4000 $50.00 800.00 $750.00 C

What if I want to use the Bal-code
code 1 or 5 =dramount - cramount
code 2,3 and 4 = cramount-dramount.

 

[Michel Walsh]

iif( code IN(1, 5), dramount-cramount, cramount-dramount)



or



(dramount-cramount) * iif( code IN(1, 5), 1, -1)




Hoping it may help,
Vanderghast, Access MVP

Can you post the SQL statement as it appears in the SQL view?

Vanderghast, Access MVP

Are D and C the only two values in Acctyp? If so, you should be able to
use:

[quoted text clipped - 20 lines]

Please help

Thanks, the problem is resolved and here is the output below:

Transaction Table
Date DocNum AccNum DrAmount CrAmount Balance Acctype Bal.-Code
27-Feb-07 501 1000 5,000.00 50.00 $4,950.00 D
1
27-Feb-07 501 3000 0.00 5,000.00 $5,000.00 C 3
27-Feb-07 501 4000 50.00 800.00 $750.00 C 4
27-Feb-07 501 1000 500.00 0.00 $500.00 D 1
27-Feb-07 501 1020 300.00 0.00 $300.00 D 1


Solution
AccNum SumOfDrAmount SumOfCrAmount Balance DRCR Code
1000 $5,500.00 $50.00 $5,450.00 D
1020 $300.00 $0.00 $300.00 D
3000 $0.00 ,000.00 $5,000.00 C
4000 $50.00 800.00 $750.00 C

What if I want to use the Bal-code
code 1 or 5 =dramount - cramount
code 2,3 and 4 = cramount-dramount.