how to return a value from a query to a variable?

[bella]

Hi

Can anyone tell me if its possible to create an Access Query and then put one of the fields returned in that query into an Integer variable ?

i want to say something like:

SELECT x,y,z
FROM X,Y,Z
WHERE blah blah & blah;

and then in VB code something like:

Dim CounterVariable As Integer

IF x = 3
THEN CounterVariable = 3
& bring up report3
IF x = 4
THEN CounterVariable = 4
& bring up report 4

etc (for 6 variables)

what is the syntax for saying this in vb???

any help wouldbe appreciated

 

[Captain Chaos]

New twist

How about a new angle?
I'm thinking on my feet here but what about this for a concept


docmd.openreport choose(dcount("X","XYZ","This = that etc"),"Rpt=1","Rpt=2","rpt=3")

If you're counting the results to choose the report then this concept might work. If you're looking for something in a field substitute dlookup for dcount

Let me know

 

[bella]

DAO recordset??

Hi Tim/anyone else out there,

thanks for your recordset advice. I am having a problem though, that my vb compiler wont recognise this line:

Dim rst as new.adodb.Recordset

I dont know how to install the ADO library - I am using DAO as I am not sure if my version of access supports ADO. Do you know how to declare a new recordset using DAO??

in urgent need...

bella

 

[fuzzygeek]

DAO ADO

Go to the following Microsoft site and there is a white paper by A. Henry that shows how to set up recordsets, set up db's, access query defs, etc. both in ADO and DAO. It should show you how to accomplish this in DAO.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

By the way it should be:
Dim rst as New adodb.recordset.