Results 1 to 5 of 5

Thread: DSUM YTD Help

  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: DSUM YTD Help

    Access noob here.

    I am trying to build a table of running year-to-date totals using the DSUM function in an expression. I have one source table, a query result called subQuery_2, with the following:

    Period (a date, using the month and year)
    District_Code (text, A, B, or C)
    Service_Code (1 through 18)
    Factor (double, assigned to district_code and service_code in another table)
    Annual_Total (double, assigned to district_code and service_code in another table)

    Monthly_Target (a calculated expression = Factor X Annual_Total)

    I have built the YTD results incrementally and they have worked at each step so far. However, when I introduce the District_Code parameter, the DSUM is bombing and I cannot understand how to fix it.

    The SQL code is
    Code:
    SELECT subQuery_2.Period, subQuery_2.District_Code, 
    subQuery_2.Service_Code, subQuery_2.Monthly_Target, 
    CDbl(DSum("subQuery_2.Monthly_Target","subQuery_2",
    " subQuery_2.District_Code =" & subQuery_2.District_Code & 
    " AND subQuery_2.Service_Code =" & subQuery_2.Service_Code &
     " AND MONTH(subQuery_2.Period)<=" & Month(subQuery_2.Period) & 
    " And YEAR(subQuery_2.Period)=" & Year(subQuery_2.Period))) AS YTD_Target
    FROM subQuery_2
    GROUP BY subQuery_2.Period, subQuery_2.District_Code, subQuery_2.Service_Code, 
    subQuery_2.Monthly_Target, Year(subQuery_2.Period), Month(subQuery_2.Period)
    ORDER BY Year(subQuery_2.Period), Month(subQuery_2.Period);
    The problem is with the expression in the DSUM criteria
    Code:
    " subQuery_2.District_Code =" & subQuery_2.District_Code & " AND subQuery_2.Service_Code =" & subQuery_2.Service_Code & " AND MONTH(subQuery_2.Period)<=" & Month(subQuery_2.Period) & " And YEAR(subQuery_2.Period)=" & Year(subQuery_2.Period)
    The query runs, but produces errors in the YTD_Target field, with the following error message:
    The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'A."

    I have tried to surround the "A" results with quotes (single and double) to no avail.

    Any advice? I can junk this approach if another route is better.
    Last edited by Jay Petrulis; 12-29-06 at 17:25. Reason: Fix SQL code wrap

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Being a text value, it would have to be surrounded by single quotes. What error do you get when you do that? And what exactly does the DSum look like at that point?
    Paul

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    using DSUM() in a query is most likely a speed disaster.
    it's bedtime for me, but if you don't get a better suggestion earlier, i'll have a look early tomorrow morning.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2006
    Posts
    4
    pbaldy,

    I inserted a single quote bracketing the problem area and it worked! Thank you.

    From:
    subQuery_2.District_Code =" & subQuery_2.District_Code & '

    To:
    subQuery_2.District_Code ='" & subQuery_2.District_Code & "'

    I thought I had tried the exact suggestion earlier, but obviously I did not do the right thing (or else something else was causing an additional problem).

    At this point, I have it working. Much obliged.

    izyrider,

    I am all for a better approach. I don't like this construct and will welcome any ideas.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    found it!

    see rudy's theta join suggestion. early in the same thread you will see Virgo-Libra's suggestion which is pretty much what you are currently doing.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •