Results 1 to 6 of 6
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    Cannot get DSum to work appropriately

    I'm newer towards the programming side of Access and have recent found the function DSum to add fields and tables, but I cannot ever get the code to run properly nor have I figured out the proper syntax. Here is a sample code I am working with:



    Code:
    Qnty: DSum([Item Quantity],[Table1],"skittles")
    Table1 is the master table and Item Quantity is a field within it. I am trying to add all quantities for the reference item name "skittles".

    Throughout multiple videos and articles, all were showing different syntax's. Using "" instead of [] and visa-versa.

    Now when I run the code, I always get prompted to Enter a Parameter which completely confuses me.

    All in short, I cannot seem to get it to work. All help is appreciated. Thanks.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You need both "" and [] for the field name argument because field name has space. The [] define name, the "" define the argument value. Don't use spaces or punctuation/special characters (underscore only exception) in naming convention and the [] usually not needed. There are some situations where they will be needed regardless.

    It is interesting that a space in table name is okay without [] in the DSum. Just tested, hadn't realized that before. However, note use of [] in the aggregrate query.
    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.

  4. #4
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Quote Originally Posted by orange View Post
    I appreciate the link, but I have already reviewed the same page multiple times because that page still doesn't explain the uses for "" and []. Also got the code to work, but it keeps asking to enter a parameter. Thanks for the help @orange and @June7. @June7, you perfectly summed up and answered exactly what I have been trying to find.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The DSUM() function has 3 parameters: expression, domain, [criteria]

    The expression is the field in the domain you want to sum. This field name must be a string: "ItemQuantity"
    The domain is the table/query you want to use to get the numbers. The domain name must be a string:"Table1"
    The criteria is like a WHERE clause in a query, without the word WHERE. This must also be a string: "ItemName = 'skittles'"

    Note the placement/usage of the quotes. Because the criteria value is a string, delimiters must be used.
    I used single quotes, but you could use double quotes.
    "ItemName = ""skittles"""

    So on a form, set the control source of a text box control to
    Code:
        =DSum("ItemQuantity","Table1","ItemName = 'skittles'")
    or
    Code:
        =DSum("ItemQuantity","Table1","ItemName = ""skittles""")
    ----------------------------------------------------------------------------

    In a query, use
    Code:
    Qnty: DSum("ItemQuantity","Table1","ItemName = 'skittles'")
    ----------------------------------------------------------------------------

    If the criteria field was a number type, you would use
    Code:
    =DSum("ItemQuantity","Table1","ItemFK = 10")
    ------------------------------------------------------------------------------

    If you have spaces in field names, the field name must be enclosed by brackets AND quotes:
    Code:
        =DSum("[Item Quantity]","Table1","ItemName = 'skittles'")

  6. #6
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Thank you so much. You explained it all. I couldn't find it anywhere.

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

Similar Threads

  1. Replies: 5
    Last Post: 05-21-2016, 02:28 PM
  2. Replies: 4
    Last Post: 12-06-2014, 08:49 PM
  3. Replies: 5
    Last Post: 08-11-2014, 10:08 AM
  4. Replies: 4
    Last Post: 05-25-2014, 02:01 AM
  5. Replies: 8
    Last Post: 05-10-2012, 10:57 AM

Posting Permissions

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