Thread: DSUM() Error

please tell me what is the error in the following function:
=dsum(Amount,Expenses,(month(ExpenseDate) = month(date())

Try:
=DSum("Amount","Expenses","Month(ExpenseDate)=" & Month(Date()))

Still getting a syntax error.

Show the exact error message. I know this syntax is correct.

The Expression you entered contains invalid syntax.

You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.

Don't know what else to offer. The syntax works for me. I assumed you are using this in the ControlSource of a textbox. This also works because the criteria Month(Date()) is composed of Access functions: =DSum("Amount","Expenses","Month(ExpenseDate)=Mont h(Date())")

Thanks for the effort. I modified the condition as follows and got the same error:

=dsum(Amount,Expenses,1=1)

I converted the DSum function into its SQL Equivalent("=(Select sum(amount) from expenses)") and got a #Name? error showing in textbox on the form.

Domain aggregate functions (DSum, DCount, DAvg, etc) require the arguments be enclosed in quote marks, unless using variables.

Can't use SQL SELECT that way because the SQL returns a recordset, not a single value. Even if the recordset has only one record with one field, it is still a recordset.

I pasted the function from Expression Builder and the argument separator is not a comma but a semi-colon it worked. Thank you June for you kind assistance.

I am using Access 2010.

I have never used the expression builder so just tested it. I see commas, not semi-colons. I have never built a domain aggregate or SQL aggregate with semi-colons. This has me confused but as long as it works for you ...

The expression builder also did not include the quote marks which means when the function executed I got parameter prompts.

Wizards and builders, bah, I avoid them.

Maybe its the different versions I am using 2007

No, I originally built my project with Access2003, upgraded to 2007 at work. Nothing different about aggregate functions. I am using Access2010 at home.

Microsoft Mystery

I was having the same problems as above. It made no sense to me at all as I was copying a query criteria straight from a textbook.

My problem was the default list separator set in the regional settings. I had changed it a while back to a "pipe" character for some CSV lists. I changed it back to a comma, the default windows setting, and all is well. This goes in line with why the semicolon was working for msr. When I got the idea that that may be the cause I tried the | in the query criteria, but no dice.