I have a Select Query that sums a field [COST]. I need to use this "answer" as a variable in several places within a routine. How do I capture the answer of SumOfCOST?
Dim Dab As Database
Dim rs as REcordset
Dim qdf As QueryDef
Set Db = CurrentDb()
set qdf = db.QueryDefs("YourQueryName"
Set rs = qdf.OpenRecordset
'
now RS!SumOfCOST gives you the column you want
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -
There is still a small problem. What you gave me works fine in my query. There are 2 fields - TicketNumber and Cost. If I use a number such as 730 for my criteria in the TicketNumber field everything is fine. I get a sumation of cost for all the ticket numbers with 730. Now to automate this...
I use a form and select ticket numbers. Then I use [forms]![frm_Redeem_Calculations]![text58] as the criteria. I get this error on the line "Set rs = qdf.OpenRecordset" ...
error 3061
too few paramaters Expected 1.
A bit puzzled on this one, I do not use qdf.OpenRecordset very often.
I can only think that you cannot have a query with criteria.
You could try this (you will have to fill it out a bit cannot remember exactly how to do this, and it is after 18:30 here in UK!
Change the criteria in you query to (say) [x]
Edit the SQl to add a Parameter statement
PARAMETER x Text;
before the SELECT
In the code I posted:
set qdf = db.QueryDefs("YourQueryName"
qdf.Parameters("x" = Forms!...etc
Set rs = qdf.OpenRecordset
I might not have got this exactly right, but if you look up PARAMETER statement or Parameters collection in help, I am sure you will be able to figure it out.
Good Luck!
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -
If you enter "Too few parameters" as a search in this forum you will get 46 or so strikes. I've wrestled with this error before and always gotten through, but I'm afraid I can't remember how just now!
I've got a database with many fields, only two fields are needed here ...TicketNumber and Cost. I need to add up the cost(could be 1 or several) for a given TicketNumber and store this number as a variable called varCost. I then will use this variable in various calculations along with customer input, Etc. It seems I've done it again and come up with a plan that is non-conventional and has caused considerable problems. Scratch the idea of a query that sums up the cost.
What would be the easiest way to do this?
I can remember doing this in FoxPro 2.6 DOS( ...I know!) and it would be an easy one-liner, "SUM COST to varSum for TicketNumber=varTicNumber"
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.