Help with DSUM

[myxmaster]

I have the following in a unbound text box.

=DSum("[Amount]", "YourTable", "[TransactionType] = ""Credit""") -
DSum("[Amount]", "YourTable", "[Status] = ""Cleared""")

This works however not until I put cleared in the status in the field.
If I have 10 credit transactions nothing will show however as soon as
I enter 1 cleared it will show that transaction. What I need to do is
show all the transactions however if cleared is entered then it will
remove that total from the sum.
TIA

 

[EeOr]

Hi, this looks like a problem with Nulls perhaps you should try the NZ
function I haven't tested this but try:

=DSum("[Amount]", "YourTable", "[TransactionType] = ""Credit""") - NZ(
DSum("[Amount]", "YourTable", "[Status] = ""Cleared"""))

That way if the DSUM of cleared transactions is null it will use 0 instead.

Jon

 

[myxmaster]

Jon
Perfect. Thanx for the quick reply, I have been looking at this for
days.

Hi, this looks like a problem with Nulls perhaps you should try the NZ
function I haven't tested this but try:

=DSum("[Amount]", "YourTable", "[TransactionType] = ""Credit""") - NZ(
DSum("[Amount]", "YourTable", "[Status] = ""Cleared"""))

That way if the DSUM of cleared transactions is null it will use 0 instead.

Jon

I have the following in a unbound text box.

=DSum("[Amount]", "YourTable", "[TransactionType] = ""Credit""") -
DSum("[Amount]", "YourTable", "[Status] = ""Cleared""")

This works however not until I put cleared in the status in the field.
If I have 10 credit transactions nothing will show however as soon as
I enter 1 cleared it will show that transaction. What I need to do is
show all the transactions however if cleared is entered then it will
remove that total from the sum.
TIA

 

[Al Campagna]

If there were no Cleared entries, I would have expected Sum of Amount to return Null,
which hould have caused an #Error.

But, try this...
=NZ(DSum("[Amount]","tblYourTable","TransactionType = ""Credit""")) -
NZ(DSum("[Amount]","tblYourTable","Status= ""Cleared"""))

Didn't have time to test, but that should do it...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."