please tell me what is the error in the following function:
=dsum(Amount,Expenses,(month(ExpenseDate) = month(date())
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()))
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Still getting a syntax error.
Show the exact error message. I know this syntax is correct.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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())")
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
Last edited by June7; 05-30-2011 at 01:25 PM.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.