DSum problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone please take a look at this? All I'm getting is #error when I try
ot run it in a report.

Some background... I need the sum of all credit invoices for a specific
account where the credit has not been applied. Field names are credit_total
(double), credit_applied (yes/no), and acct_no (text).

This is what I've got so far:

=DSum("credit_total","credits","(acct_no = " & [acct_no] & ") AND
(credit_applied = False)")

Not all accounts have credits so it's highly possible that there will be no
result.

Thanks for any help provided.
 
Hi Patrick,

I suspect that this arises because you have both fields in your recordsource
and controls on your form/report with the same name. When Access tries to
evaluate the dSum, it can't resolve this issue. Rename the controls holding
credit_total, acct_no and credit_applied to txtCredit_total, txtAcct_no, and
chkCredit_applied and all should be well.

HTH,

Rob
 
Rob,

No joy on this! I may not have made myself clear on this - none of the
fields for the credit (credit_total, credit_applied) are not on the report.
If I put them on the report it screws the report up. Maybe I need to go a
different way? Ideally, if I could assign a SQL to a variable, or a function
return value, I think I'd have it. Your thoughts?

Thanks.... more ideas are appreciated!!!

Rob Parker said:
Hi Patrick,

I suspect that this arises because you have both fields in your recordsource
and controls on your form/report with the same name. When Access tries to
evaluate the dSum, it can't resolve this issue. Rename the controls holding
credit_total, acct_no and credit_applied to txtCredit_total, txtAcct_no, and
chkCredit_applied and all should be well.

HTH,

Rob

Patrick said:
Can someone please take a look at this? All I'm getting is #error when I
try
ot run it in a report.

Some background... I need the sum of all credit invoices for a specific
account where the credit has not been applied. Field names are
credit_total
(double), credit_applied (yes/no), and acct_no (text).

This is what I've got so far:

=DSum("credit_total","credits","(acct_no = " & [acct_no] & ") AND
(credit_applied = False)")

Not all accounts have credits so it's highly possible that there will be
no
result.

Thanks for any help provided.
Click to expand...
Click to expand...
 
Try this:

=Nz(DSum("[credit_total]","credits","[acct_no] = " & Me.acct_no & " AND
[credit_applied] = " & False,0)

The Nz() function wrapping the DSum() will ensure a '0' is returned if Null.

Steve
 
i'm guessing the problem is the syntax. try

=DSum("credit_total","credits","acct_no = " & [acct_no] & " AND
credit_applied = False")

the above goes on one line in the control's ControlSource property, of
course, regardless of line-wrap in this post.

the field [acct_no] must be included in the report's RecordSource, of
course. if it's *not* already bound to a control in the report itself
(either visible or invisible, doesn't matter), then you may have to do that
before Access will "see" it - and then reference the controlname rather than
the fieldname, in the above expression.

hth
 
Thanks Steve, but that created more problems! In the Dxxx() functions,
Me.<control_name> is evaluated as [Me].[<control_name], thus the function is
looking for a control named [Me]. in any event, you got me going on
something I completely forgot about - each expression must evaluate to a
string - so jusicious use of quotation marks is needed.

Here's what I finally came up with:

=DSum("[credit_total]","credits","'[acct_no] = ' & [txtacct_no] AND
'[credit_applied] = ' & False")

Note the use of single quotes around the arguments which in turn are
surrounded by double quotes.

Thanks to all for the help. A function to do this would be an easier method
- it could be attached to the onLoad event and life would be easy. If I have
some time I'll kick that around.

Again, thanks to all!!!

Patrick

SteveM said:
Try this:

=Nz(DSum("[credit_total]","credits","[acct_no] = " & Me.acct_no & " AND
[credit_applied] = " & False,0)

The Nz() function wrapping the DSum() will ensure a '0' is returned if Null.

Steve


Patrick said:
Can someone please take a look at this? All I'm getting is #error when I try
ot run it in a report.

Some background... I need the sum of all credit invoices for a specific
account where the credit has not been applied. Field names are credit_total
(double), credit_applied (yes/no), and acct_no (text).

This is what I've got so far:

=DSum("credit_total","credits","(acct_no = " & [acct_no] & ") AND
(credit_applied = False)")

Not all accounts have credits so it's highly possible that there will be no
result.

Thanks for any help provided.
Click to expand...
Click to expand...
 
Back
Top