Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    4

    DSUM HELP! I am frustrated

    I have a Table called "NCR", In that table, in that table I have a material weight that is entered "MaterialWeight".



    Totaled at the bottom of the report in the footer by:

    Total Weight for the week: =Sum([MaterialWeight]) This gives me the total weight that is Nonconfoming for the week.

    Out of that total I have different types of Nonconforming material input in a field called the "Disposition". In that "Disposition" column we enter one of the following:

    SCRAP, RECLASS, USE AS IS or RETURN

    I want this formula to total the "MaterialWeight" if my input for "Disposition" = SCRAP

    =DSum("[MaterialWeight]","[Disposition]=SCRAP")

    Whit this i get the output of #error

    This doesnt work either
    =DSum("[MaterialWeight]","NCR","[Disposition]=SCRAP")

    With this i get no output what so ever

    =DSum("[MaterialWeight]","NCR","[Disposition]='SCRAP'")

    With this i get 17,654 which is the total in Table NCR, This works. Now I have to get it to just pull the MaterialWeight from the QUERY "NCR Weekly Query" not the entire report

    =DSum("[MaterialWeight]","NCR Weekly Query","[Disposition]='SCRAP'")

    With this i get #error, I need this formula to take into consideration the date range and the data pulled by the query.

    Thanks in advance

    I have a screen shot of the program with pbasic info to further understand what I am talking about.



    http://i13.photobucket.com/albums/a2.../DSUMQUERY.jpg

  2. #2
    jya is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2007
    Location
    Chicagoland
    Posts
    109

    I'm sure there is a better way

    I am not an Access expert, but for what it's worth...

    I assume that your 'NCR Weekly Query' takes parameters (probably a date criteria) which is probably why you got the error. DSum will take a query without parameters.

    Messing around, I guessed at what your 'NCR Weekly Query' looked like and created a very simplistic query. I copied this query and saved it as a new query 'Weekly Scrap Query' and changed it to a 'Total' query. In the 'Total' row, I selected 'Sum' for 'MaterialWeight' and 'Where' for 'Disposition' and for my date field. I entered 'Scrap' for the criteria under 'Disposition'. Keep the exact same criteria in the date field that was used in your 'NCR Weekly Query'. No other fields should be in the 'Scrap' query.

    In the 'NCR Weekly Query', I added my 'Weekly Scrap Query' to it and added my Scrap MaterialWeight sum field to the 'NCR Weekly Query'.

    In the 'NCR Weekly Report', I selected the 'Scrap MaterialWeight' in replace of your DSum expression. This is probably a poor way to do this, but it appears it will work.

  3. #3
    Join Date
    May 2006
    Posts
    4
    Thanks for your assistance. I will see if I can duplicate that.

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

Similar Threads

  1. DSUM Problems
    By swampdonkey in forum Forms
    Replies: 2
    Last Post: 09-29-2006, 10:52 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