Access query running total with combobox value

Me again - another question on running totals.  Thanks to help from this site, I have my problem narrowed down to this:

I have a query (QueryA) that totals by month based on a date I enter:

Select Sub_Prod_Name, MonNum, Sum(Commission) from TableA
GROUP BY Sub_Prod_Name, MonNum
HAVING ((TableA.TR_Bill_Yr)=Right([Forms]![Accounting Reports]![Booking Year],2)
ORDER BY Sub_Prod_Name, MonNum;

It produces this

Dave        1          10
Dave         2         30
Dave         3         10
Mike         1          50
Mike         2           20
Mike         3           10

All good so far.

I run a query (QueryB) on QueryA to produce a running total:

SELECT Sub_Prod_Nam, MonNum, Commission, DSum("[Commission]", "QueryA","[Sub_Prod_Nam] ='" & Sub_Prod_Nam & "'  And [MonNum]<= " & [MonNum]) AS RunTot
ORDER BY Sub_Prod_Nam, MonNum;

If I hardcode a date in Query A, replacing this
Right([Forms]![Accounting Reports]![Booking Year],2)
with "13"

it all works.  If I have the Forms variable in there, so the user can select a date on the form,

QueryB gives me #Error in RunTot

Why does QueryB care whether the date comes in from the form or not?

Thanks -
Who is Participating?
GrahamMandenoConnect With a Mentor Commented:
The problem is related to the fact that the Jet database engine (DAO) has no knowledge of Access forms and how to resolve references to them.  Only the Access UI has this knowledge.

When you run QueryA, Access sees the [Forms] reference and resolves it before passing the query over to the database engine.  However, when you run QueryB, containing a DSum which refers to "QueryA" as a text string only, the Access UI is not clever enough to realise that there is a [Forms] reference buried in there.  When the DAO engine tries to resolve it, it fails.

I suggest you do this with a single query containing a subquery:
SELECT Sub_Prod_Name, MonNum, Sum(Commission),
    (Select Sum(Commission) from TableA as RT 
        where (RT.Sub_Prod_Name = TableA.Sub_Prod_Name)
          and (RT.TR_Bill_Yr=TableA.TR_Bill_Yr)
          and (RT.MonNum<=TableA.MonNum) ) as RunTot
FROM TableA 
GROUP BY Sub_Prod_Name, MonNum
WHERE TableA.TR_Bill_Yr=Right([Forms]![Accounting Reports]![Booking Year],2);

Open in new window

Best wishes,
Graham Mandeno [Access MVP 1996-2013]
loybobAuthor Commented:
Yeah, Graham, that looks good - I'll try it in the morning and let you know.  Thank you so much!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.