Using the DSum Function for the first time

[Jessica240]

Hello. I am new to the DSum function and could use some guidance. I have tried researching it, but am not finding the info to really help me understand why it won't work in my report.

I tried using this function in a query and it worked just fine. But for some reason when I put it in a text box in a report the result is "#Error" or "Name?"

My report is linked to a query - the name of this query is "production combined for report". I have a field called "Payable" that I need the values summed if the value in the "department" field is "Kitchen"

(I have no idea how to do SQL or VBA by the way) So then this is my code:

DSum("[payable]", "[production combined for report]", "[department]= kitchen")

I'm not sure if I'm screwing up on the placement of the brackets and/or the quotation marks?



Thanks in advance for the help!

[ssanfu]

"Kitchen" is a text literal. In this case, it needs to be surrounded with single quotes because it is within double quotes.

Try
DSum("[payable]", "[production combined for report]", "[department]= 'kitchen'")


PS ... shouldn't use spaces in object names...

[June7]

I don't think you need DSum. Domain aggregates can be slow performers in query or textbox. Also, your expression doesn't consider if the report has been filtered by maybe a date period or some other criteria. So unless you want a sum that doesn't synchronize with the records selected on report, use your DSum. Otherwise, since the fields are part of the report RecordSource, use Sum() with IIf() in expression in textbox placed in footer section.

=Sum(IIf([Department]="kitchen", [payable], 0))

Or construct the report with a Department grouping and Sum expression in group footer: =Sum([Payable])

BTW, without the apostrophes the DSum should fail in query as well.

[Jessica240]

Wow that was a quick fix. Thank you both for your very useful information. Your replies helped me out tremendously!

[Jessica240]

Sorry.. One more quick question: If I want to tell access to sum the values if the department is "kitchen" or "maintenance" can I do something like this:

DSum("[payable]", "[production combined for report]", "[department]= 'kitchen' or 'maintenance'")

That doesn't work so I'm sure I need to fix the language in there somewhere...

[June7]

Have to repeat the field name:

"department='kitchen' Or department='maintenance'"

[Jessica240]

I wondered about that. Gosh now I feel dumb. But nonetheless, thank you!

[Jessica240]

Also I should mention: WOW, June! You were not kidding about the dsum function being a slow performer! Oh my goodness it is terribly slow!!!