Results 1 to 15 of 15

DSUM() Error

  1. #1
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17

    DSUM() Error

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

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    Try:
    =DSum("Amount","Expenses","Month(ExpenseDate)=" & Month(Date()))
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17
    Still getting a syntax error.

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    Show the exact error message. I know this syntax is correct.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17
    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.

  6. #6
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    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())")
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17
    Thanks for the effort. I modified the condition as follows and got the same error:

    =dsum(Amount,Expenses,1=1)

  8. #8
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17
    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.

  9. #9
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    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 12:25 PM.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  10. #10
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17
    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.

  11. #11
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  12. #12
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17
    Maybe its the different versions I am using 2007

  13. #13
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    No, I originally built my project with Access2003, upgraded to 2007 at work. Nothing different about aggregate functions. I am using Access2010 at home.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  14. #14
    msr71 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    17
    Microsoft Mystery

  15. #15
    PhilA is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    1

    Mystery solved... (for me anyways)

    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 12-05-2011, 03:10 AM
  2. Help with Sum and DSum
    By objNoob in forum Reports
    Replies: 5
    Last Post: 12-04-2011, 11:55 PM
  3. How do I use the DSum
    By Ironclaw in forum Access
    Replies: 1
    Last Post: 08-25-2010, 06:35 AM
  4. Help with dsum
    By bjsbrown in forum Reports
    Replies: 6
    Last Post: 02-06-2010, 07:33 AM
  5. DSUM HELP! I am frustrated
    By PnerraD in forum Reports
    Replies: 2
    Last Post: 09-24-2007, 01:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums