DSUM expression on longer working

[KRS@WAC]

I'm a newb, studying Access and trying to get a reporting database going again after sitting dormant for some time. I've solved several issues, but this one is stumping me. Any help appreciated, thanks!

This used to work, I've see old reports where it did. Now it returns #ERROR

=DSum("[Total]","Total Loans","Date Between #" & [Start Date] & "# And #" & [End Date] & "#")

Total is the column, Total Loans is the query, the expression works if I take out the date criteria, but I need the sum between a data range. This appears in the footer section of a report, but this table is NOT part of the query the report is built on.

Screen Cap of the query "Total Loans" is attached.

[June7]

So what has changed since it last worked?

Next time, Instead of link to image, try embedding in the post - make it easier for readers to help you.

Date is a reserved word and really should not use reserved words as names for anything.

Try enclosing Date in [], like: [Date] Between

If still need help, post the query SQL statement or provide the db. Follow instructions at bottom of my post.

[KRS@WAC]

So what has changed since it last worked?

Next time, Instead of link to image, try embedding in the post - make it easier for readers to help you.

Date is a reserved word and really should not use reserved words as names for anything.

Try enclosing Date in [], like: [Date] Between

If still need help, post the query SQL statement or provide the db. Follow instructions at bottom of my post.

IDK what could have changed, I just took over this thing. Here is the SQL

=DSum("[Total]","Total Loans","Date Between #" & [Start Date] & "# And #" & [End Date] & "#")

[June7]

You already told us that. Did you try the suggestion?

[KRS@WAC]

Tried adding the brackets around [Date], made no difference. The report generates, this field is blank then after a few seconds, "#ERROR" populates. Could the delay be significant?

[June7]

Domain aggregate functions are known to perform slowly on large datasets.

If no records meet the criteria function should return 0.

I am afraid not able to determine cause without directly viewing db.

[beilstwh]

Actually if it can't find any rows it returns null not zero which would display as #error. Wrap the dsum inside an NZ function so if it is null it sets the sum to 0

[June7]

Oops, correct.

DCount() would return 0.

Good catch beilstwh.

So now the question is whether or not there are records that should meet the criteria?

[beilstwh]

I would set a break point at the dsum and see what the variables start date and end date contains and then run a query against the dataset using the same filter conditions

[moke123]

In addition to bracketing Date as June suggested, you should also bracket "[Total Loans]" as you have a space in the name.

[June7]

That does seem to be needed, moke, but if you test, it will work without the brackets on the table/query name. Weird, right! But certainly won't hurt to use.

Really advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.