Assign a variable from query

[cctonto]

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?

 

[KenReay]

Hi

Using DAO

Dim Dab As Database
Dim rs as REcordset
Dim qdf As QueryDef

Set Db = CurrentDb()
set qdf = db.QueryDefs("YourQueryName&quot
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 -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

oops, must learn to type

Dim Db As Database Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cctonto]

Hi Ken,

You helped me before... Thanks again

Steve
Tucson, AZ

 

[cctonto]

Ken,

Hope you drop by this question again.

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.

Thanks,
Steve
Tucson, AZ

 

[KenReay]

Hi

Is the form frm_Redeem_Calculations open at this point, you can only address items on a form using this syntax if the form is open Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cctonto]

Yes the form is open and I have a text field displaying the ticket number on the screen.

Steve

 

[KenReay]

Hi Steve

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&quot
qdf.Parameters("x&quot = 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 -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[smandoli9]

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!

 

[cctonto]

Alright... Plan B

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"


Any thoughts on Plan B?
Thanks,

Steve
Tucson, AZ