Iif expression in query

[anyaley via AccessMonster.com]

I have an expression which subtracts total debit from total credit for each
account group of transactions to give the balance in a calculated field
called Balance. i.e Balance: Sum(([CrAmount]-[DrAmount])) It works OK. But
now I want to make it conditional based on field named Acctype.

If Acctype is "D", Balance: Sum(([DrAmount]-[CrAmount]))
If Acctype is "C", Balance: Sum(([CrAmount]-[DrAmount]))

How can I use one expression using iif condition in my query. I tried
something like this:
iif ([Acctype]="D",Sum(([DrAmount]-[CrAmount])) ,Sum(([CrAmount]-[DrAmount]))
It gave aggregate error message. Please help.

Alex

 

[John Spencer]

Assumptions:
AccType is always populated
Acctype is always D or C

Try the following
SUM(IIF(AccType="D",DrAmount,CrAmount) - IIF(AccType="D",CrAmount,DrAmount))

OR if AccType is not always D or C

Sum(IIF(AccType="D" or AccType ="C",
IIF(AccType="D",DrAmount,CrAmount) -
IIF(AccType="D",CrAmount,DrAmount),
Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[anyaley via AccessMonster.com]

Assumptions:
AccType is always populated
Acctype is always D or C

Try the following
SUM(IIF(AccType="D",DrAmount,CrAmount) - IIF(AccType="D",CrAmount,DrAmount))

OR if AccType is not always D or C

Sum(IIF(AccType="D" or AccType ="C",
IIF(AccType="D",DrAmount,CrAmount) -
IIF(AccType="D",CrAmount,DrAmount),
Null)

I have an expression which subtracts total debit from total credit for each
account group of transactions to give the balance in a calculated field

[quoted text clipped - 11 lines]

Alex

Account Type is always either "D" or "C".
If "D" subtract sum of CrAmount from DrAmount.
If "C" subtract sum of DrAmount from CrAmount. but I dont see your
subtraction in the above. Please clarify.

 

[John Spencer]

I've broken this into multiple lines, so I could comment on the operation

SUM( <<-- Sum the result of the following calculation
IIF(AccType="D",DrAmount,CrAmount) <<-- Get DrAmount or CrAmount
- << --Subtract
IIF(AccType="D",CrAmount,DrAmount) <<-- Get CrAmount or DrAmount
)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Assumptions:
AccType is always populated
Acctype is always D or C

Try the following
SUM(IIF(AccType="D",DrAmount,CrAmount) -
IIF(AccType="D",CrAmount,DrAmount))

OR if AccType is not always D or C

Sum(IIF(AccType="D" or AccType ="C",
IIF(AccType="D",DrAmount,CrAmount) -
IIF(AccType="D",CrAmount,DrAmount),
Null)

I have an expression which subtracts total debit from total credit for
each
account group of transactions to give the balance in a calculated field

[quoted text clipped - 11 lines]

Alex

Account Type is always either "D" or "C".
If "D" subtract sum of CrAmount from DrAmount.
If "C" subtract sum of DrAmount from CrAmount. but I dont see your
subtraction in the above. Please clarify.

 

[anyaley via AccessMonster.com]

I've broken this into multiple lines, so I could comment on the operation

SUM( <<-- Sum the result of the following calculation
IIF(AccType="D",DrAmount,CrAmount) <<-- Get DrAmount or CrAmount
- << --Subtract
IIF(AccType="D",CrAmount,DrAmount) <<-- Get CrAmount or DrAmount
)

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 - 22 lines]

If "C" subtract sum of DrAmount from CrAmount. but I dont see your
subtraction in the above. Please clarify.

 

[John Spencer]

Can you copy and paste the SQL of the query that is failing? (In menu -
View: SQL)

Try putting the expression in the field without the word SUM and then
putting sum in the Total row

Field: IIF(AccType="D",DrAmount,CrAmount) -
IIF(AccType="D",CrAmount,DrAmount)
Total: Sum


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've broken this into multiple lines, so I could comment on the operation

SUM( <<-- Sum the result of the following calculation
IIF(AccType="D",DrAmount,CrAmount) <<-- Get DrAmount or CrAmount
- << --Subtract
IIF(AccType="D",CrAmount,DrAmount) <<-- Get CrAmount or DrAmount
)

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 sum

Assumptions:
AccType is always populated

[quoted text clipped - 22 lines]

If "C" subtract sum of DrAmount from CrAmount. but I dont see your
subtraction in the above. Please clarify.

 

[anyaley via AccessMonster.com]

Can you copy and paste the SQL of the query that is failing? (In menu -
View: SQL)

Try putting the expression in the field without the word SUM and then
putting sum in the Total row

Field: IIF(AccType="D",DrAmount,CrAmount) -
IIF(AccType="D",CrAmount,DrAmount)
Total: Sum

I've broken this into multiple lines, so I could comment on the operation
[quoted text clipped - 22 lines]
If "C" subtract sum of DrAmount from CrAmount. but I dont see your
subtraction in the above. Please clarify.

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.

 

[John Spencer]

Then change the IIF statement to
IIF([Bal-Code] in (1,5), DrAmount,CrAmount)

Try to work out the rest of it.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Can you copy and paste the SQL of the query that is failing? (In menu -
View: SQL)

Try putting the expression in the field without the word SUM and then
putting sum in the Total row

Field: IIF(AccType="D",DrAmount,CrAmount) -
IIF(AccType="D",CrAmount,DrAmount)
Total: Sum

I've broken this into multiple lines, so I could comment on the
operation

[quoted text clipped - 22 lines]

If "C" subtract sum of DrAmount from CrAmount. but I dont see your
subtraction in the above. Please clarify.

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.