query doesn't have any results

[bmelv via AccessMonster.com]

hi,
im trying to have a bank account balance cell on a form, this is my
expression:

=Sum([Deposit])-Sum([Payment])+DSum("[payment]","[-]")+DSum("[payment]","[++]
")-DSum("[deposit]","[+]")

=Sum([Deposit])-Sum([Payment]) ----- payment and Deposit are coming from the
query that the form is linked to, no dramas here.

+, ++ and - are all queries that return results based on certain parameters,
the queries work fine.

the whole calculation works fine IF all 3 queries return at least one result,
but it breaks down (in the box that it displays in, nothing comes up, there's
no error message or anything) if any one of those queries doesn't return any
results....

any help much appreciated.
cheers
b

 

[Marshall Barton]

im trying to have a bank account balance cell on a form, this is my
expression:

=Sum([Deposit])-Sum([Payment])+DSum("[payment]","[-]")+DSum("[payment]","[++]
")-DSum("[deposit]","[+]")

=Sum([Deposit])-Sum([Payment]) ----- payment and Deposit are coming from the
query that the form is linked to, no dramas here.

+, ++ and - are all queries that return results based on certain parameters,
the queries work fine.

the whole calculation works fine IF all 3 queries return at least one result,
but it breaks down (in the box that it displays in, nothing comes up, there's
no error message or anything) if any one of those queries doesn't return any
results....

The domain aggregate functions return Null when they have no
data to operate on. Use the Nz function to deal with the
Null values. E.g.

. . . + Nz(DSum("[payment]","[-]"),0) + . . .

 

[bmelv via AccessMonster.com]

perfect thanks a lot works like a charm!

im trying to have a bank account balance cell on a form, this is my
expression:

[quoted text clipped - 12 lines]

no error message or anything) if any one of those queries doesn't return any
results....

The domain aggregate functions return Null when they have no
data to operate on. Use the Nz function to deal with the
Null values. E.g.

. . . + Nz(DSum("[payment]","[-]"),0) + . . .