How to handle multiple query results in Access 2000 1

[vamoose]

I am using the following query to return one result and assign it to a variable in Access 2000:

SQLa = "Select Actual from SameAs where SA1 = '" & Coil & "';"
Set rs = conn.Execute(SQLa, , adCmdText)
SA1 = rs.Fields("Actual").Value

My question is how to handle the results being assigned to a variable if more that 1 result is returned.
Thank you for the assistance.

 

[lewds]

Hi...

If you only want one item returned, why is there a possibity of multiple items for your criteria?

With your current SQL you could try " Select TOP 1....

or

Your could use DLookup and return the first instance...

 

[JoeAtWork]

My question is how to handle the results being assigned to a variable if more that 1 result is returned.

That entirely depends on what you want to do with the data. You can choose to completely ignore the other records, if you want (you realize that SA1 is only getting assigned the "Actual" value of the current record?).

 

[vamoose]

I would like all instaces returned and there is the possibility that there could be more than 1 result

 

[vamoose]

If there are more than 1 resulting records then I need to assign it to a variable. Thank you.

 

[JoeAtWork]

Make the variable an array and use the GetRows method of the recordset to copy it's values to the array.

 

[SkipVought]

dim fld as field, nRow as long
SQLa = "Select Actual from SameAs where SA1 = '" & Coil & "';"
Set rs = conn.Execute(SQLa, , adCmdText)
nRow = 1
do 
for each fld in rs.fields
   msgbox fld.name & " equals " & fld.value & " in row " & nrow
next
rs.movenext
nrow = nrow + 1
loop until rs.eof

Skip,

_[red][/red]