Re: What do I need?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

To assign the values from a column in each row in a query's result set you'd
iterate through a recordset based on the query:

Dim rst As ADODB.Recordset
Dim strQuery As String
Dim strFirst As String, strSecond As String, strThird As String
Dim n as Integer

strQuery = "YourQuery"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strQuery, _
CursorType:=adOpenKeyset

With rst
For n = 1 to 3
Select Case n
Case 1
strFirst = .Fields("YourFieldName")
Case 2
strSecond = .Fields("YourFieldName")
Case 1
strThird = .Fields("YourFieldName")
End Select
.MoveNext
Next n
End With

Set rst = Nothing

Rather than declaring three separate variables, however, a better solution
would be to declare a three-row static array and assign each value to a row
in the array.

To achieve the sort of things you want, though, such as identifying auditors
who have not audited a site on the occasions of its last three audits queries
of the type both Kingston and I have suggested would be a more usual and more
efficient approach. Don't think of databases in the same way as
spreadsheets. They are very different animals.

Ken Sheridan
Stafford, England

"bigbore50@xxxxxxxxx" wrote:

I guess what i am trying to say is how do you use a query just like you
would a table

i have the query but i only want to assign certain values to variables.


Lets says i have a query that looks like

Primary # Person Name Address
12345 Bill 001
tall oak
12345 Sue 111 big
road
12345 Rachael 123 broad
lane


Ok lets say that is how my query looks... and i want to know the value
that is in column b2,b3,b4 and assign those values to variables

Variable1 = bill
variable2 = Sue
Variable3 = Rachael

You know like in EXCEL you can look up a certain cell. I have a query
that returns my values i just need to know how to get the information
out of the query piece by piece and use it as criteria.

I am trying to make a scheduling database that looks at a lot of
variables.
Such as where the auditor lives and where the audit is located. etc.

I need to be able to assign variables to certain aspects of my queries
so i can use them later.

Thanks for your help

Maybe i am thinking about this totally wrong



.

Quantcast