Results 1 to 5 of 5
  1. #1
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44

    Dsum criteria

    Using DSum in a query field expression. I know the criteria in the DSum function can be tricky, having trouble getting this to work.



    I have the following query expression:

    OH Cost: DSum("[Total OH]","WeeklyCost","[Week]>="&Str([TrnsplWk])&" And [Week]<"&Str([RdyWk])"")



    [Week] and [Total OH] are the columns in the WeeklyCost table (this table is not part of the query join).

    [TrnsplWk] and [RdyWk] are fields in query

    Trying to sum [Total OH] where [Week] is greater than or equal to [TrnsplWk] and less than [RdyWk].

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Why is it not working - error message, wrong results, nothing? The quote marks at the end should cause an error. Why use the Str function? Show sample data.
    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.

  3. #3
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44
    Database attached.

    Here is the expression that works:

    OH Cost: DSum("[Total OH]","WeeklyCost","[Week]>=#3/20/2011# And [Week]<#4/3/2011#")

    Here I have inserted actual dates for the criteria limits and the result is 1.34 which is the sum of week 3/20/2011 and 3/27/2011. It is the OH Cost field expression in the OH Cost query in the attached database.

    In the expression trying to substitute [TrnsplWk] and [RdyWk] fields in the query for the absolute dates in the expression. That way, each record will have OH Cost based on the [TrnsplWk] and [RdyWk] fields for the record.

    thanks for taking a look

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    This seems to work:
    OH Cost: DSum("[Total OH]","WeeklyCost","[Week]>=#" & [TrnsplWk] & "# And [Week]<#" & [RdyWk] & "#")

    Why does TotalOH not always equal the sum of the OH fields? Why are you entering TotalOH in table anyway? This should be a calculation of the OH fields.
    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.

  5. #5
    bdaniel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    44

    Talking Solved



    Thank you for the proper syntax on that criteria with the date fields. Now I get it. Very helpful for this and future projects.

    There is extra data in the OH Cost table, not the best practice, but just including data from a spreadsheet that can be easily cut and paste into the table.


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

Similar Threads

  1. Dsum criteria problem
    By leonhuynh2006 in forum Queries
    Replies: 3
    Last Post: 10-04-2011, 03:18 AM
  2. DSum criteria using a variable that has been defined
    By beanhead0321 in forum Programming
    Replies: 5
    Last Post: 07-24-2011, 09:57 PM
  3. How to use a create a DSum with multiple criteria
    By FlyingDisc in forum Reports
    Replies: 1
    Last Post: 01-05-2011, 08:31 PM
  4. DSUM with date criteria from form
    By krutoigoga in forum Reports
    Replies: 4
    Last Post: 07-28-2010, 01:32 PM
  5. Replies: 1
    Last Post: 05-05-2010, 01:54 AM

Tags for this Thread

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