Expression subtracts double

[myxmaster]

I am almost there, however when DEBIT is entered the calculation
subtracts double the amount. For example

Credit 500
Credit 500
Debit 500

The result will show 0 instead of $500

Here is the expression:
=DSum("Amount", "YourTable", "TransactionType = ""Credit""") -
Nz(DSum("Amount", "YourTable", "TransactionType = ""Debit"""),0)

TIA

 

[Joel Padmore]

Try this instead

= DSum("Amount", "YourTable", "TransactionType = 'Credit'") -
Nz(DSum("Amount", "YourTable", "TransactionType = 'Debit'"), 0)

Note the use of the single quotes around the alphanumber values for the
TransactionType in my example

 

[Joel Padmore]

There's nothing wrong with using two double quotes ... but I find it easier
to read using the Single Quote

 

[myxmaster]

Try this instead

= DSum("Amount", "YourTable", "TransactionType = 'Credit'") -
Nz(DSum("Amount", "YourTable", "TransactionType = 'Debit'"), 0)

Note the use of the single quotes around the alphanumber values for the
TransactionType in my example










- Show quoted text -

HI Joel
Here is the code I am using. I just tried it with the single quotes
but still have the same problem. Any ideas
=DSum("[Amount]","YourTable","[TransactionType] =""Credit""")-
NZ(DSum("[Amount]","YourTable","[Status] =""Cleared"""))-
DSum("[Amount]","YourTable","[TransactionType] =""Debit""")

 

[Joel Padmore]

This expression works for me ... as the control source for a textbox ....
=DSum("Amount","YourTable","TransactionType =
'Credit'")-Nz(DSum("Amount","YourTable","TransactionType =
'Cleared'"),0)-Nz(DSum("Amount","YourTable","TransactionType = 'Debit'"),0)

This works in the immediate window
? DSum("Amount","YourTable","TransactionType =
'Credit'")-Nz(DSum("Amount","YourTable","TransactionType =
'Cleared'"),0)-Nz(DSum("Amount","YourTable","TransactionType = 'Debit'"),0)



--
Joel Padmore, Raleigh, NC
MS Access MVP

Try this instead

= DSum("Amount", "YourTable", "TransactionType = 'Credit'") -
Nz(DSum("Amount", "YourTable", "TransactionType = 'Debit'"), 0)

Note the use of the single quotes around the alphanumber values for the
TransactionType in my example










- Show quoted text -

HI Joel
Here is the code I am using. I just tried it with the single quotes
but still have the same problem. Any ideas
=DSum("[Amount]","YourTable","[TransactionType] =""Credit""")-
NZ(DSum("[Amount]","YourTable","[Status] =""Cleared"""))-
DSum("[Amount]","YourTable","[TransactionType] =""Debit""")