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
FROM QueryA
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 -
loybobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamMandenoCommented:
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]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
loybobAuthor Commented:
Yeah, Graham, that looks good - I'll try it in the morning and let you know.  Thank you so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.