Problem with iif condition in expression

  • Thread starter anyaley via AccessMonster.com
  • Start date
A

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
 
M

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
 
D

Duane Hookom

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]))
 
A

anyaley via AccessMonster.com

Michel said:
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 Balance:(your solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression
 
A

anyaley via AccessMonster.com

Duane said:
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 Balance:(your solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression
[quoted text clipped - 7 lines]
Please help
 
M

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


anyaley via AccessMonster.com said:
Michel said:
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 Balance:(your solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression
 
M

Michel Walsh

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


Vanderghast, Access MVP


anyaley via AccessMonster.com said:
Duane said:
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 Balance:(your solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression
[quoted text clipped - 7 lines]
Please help
 
D

Duane Hookom

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


anyaley via AccessMonster.com said:
Duane said:
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 Balance:(your solution)
Table tbmaccts tbtaccts tbtaccts
Total Group sum sum expression
[quoted text clipped - 7 lines]
Please help
 
A

anyaley via AccessMonster.com

Michel said:
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.
 
A

anyaley via AccessMonster.com

Duane said:
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.
 
M

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


anyaley via AccessMonster.com said:
Michel said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top