MS Access 2007查询中DSUM()函数累计总计条件失效问题求助

Got it, let's work through this DSUM problem together—this kind of behavior is super common when mixing domain aggregate functions with calculated fields and date conditions in Access. Here's how to fix it step by step:

1. Fix the Date Condition Formatting #

The most likely culprit here is how you're formatting the date condition in DSUM. Access requires dates to be wrapped in # characters, and when referencing a field from the current query, you need to concatenate it properly as a string.

Instead of a raw condition like TransactionDate <= [TransactionDate], try this structure:

DSUM("TXValue", "ACTransactionViewQuery", "TransactionDate <= #" & Format([TransactionDate], "yyyy-mm-dd") & "#")

Using Format() ensures the date is parsed correctly regardless of your system's regional settings, which often causes blank results when dates are misinterpreted.

2. Verify the Domain and Field Names #

Double-check that:

• The domain name (ACTransactionViewQuery) is spelled exactly as it appears in your Access object list (Access is case-insensitive, but typos will break things).
• The TXValue calculated column is valid in the domain query. If TXValue relies on other calculated fields in ACTransactionViewQuery, try replacing it with the raw calculation (e.g., [Debit]-[Credit]) directly in the DSUM to rule out alias issues:

  DSUM("[Debit]-[Credit]", "ACTransactionViewQuery", "TransactionDate <= #" & Format([TransactionDate], "yyyy-mm-dd") & "#")
  

3. Test with Simplified Conditions First #

Isolate the issue by testing a stripped-down version of the DSUM to confirm it works without dynamic conditions:

• Start with DSUM("TXValue", "ACTransactionViewQuery", "1=1")—this should return the total sum just like when you removed your conditions.
• Then add a hardcoded date to test the condition logic:

  DSUM("TXValue", "ACTransactionViewQuery", "TransactionDate <= #2024-05-20#")
  

If this returns a valid number, the problem is definitely with how you're referencing the dynamic date from your query.

4. Rule Out Circular References #

Since your domain is the same query that contains the DSUM calculation, make sure you're not creating a circular reference. For example, don't use the DSUM result itself as part of the TXValue calculation—stick to base table fields for both the TXValue logic and the DSUM condition.

5. Check for Null Values #

If any TXValue records are null, DSUM will ignore them by default, but if all matching records are null, you'll get a blank result. Add a NZ() function to handle nulls:

DSUM("NZ(TXValue, 0)", "ACTransactionViewQuery", "TransactionDate <= #" & Format([TransactionDate], "yyyy-mm-dd") & "#")

内容的提问来源于stack exchange，提问作者user7425513