SQL WHERE syntax question

[VickyC]

hi to all

My code opens a DAO recordset based on the following SQL....

strSQL = "SELECT * FROM " & strTbl & " WHERE " & strTbl & ".[Factor] IN(" & arrF(0) & ", " & arrF(1) & ..... & arrF(12) & ")"

This works perfectly, but the array values must all be listed individually in the 'IN' clause. Whenever I use this code, I can have more or less values in the array. Here's my question...

Is there a way to simply use something like...

strSQL = "SELECT * FROM " & strTbl & " WHERE " & strTbl & ".[Factor] IN(" & arrF & ")"

I can't seem to get a syntax that works in the 'IN' clause..

Thank you
Vicky

 

[Andrzejek]

Would you tell us what you get in Immediate Window when you do that:
[codee]
strSQL = "SELECT * FROM " & strTbl & " WHERE " & strTbl & ".[Factor] IN(" & arrF(0) & ", " & arrF(1) & ..... & arrF(12) & ")"
[blue]
Debug.Print strSQL
Debug.Print "arrF(0) is " & arrF(0)
Debug.Print "arrF(1) is " & arrF(1)
Debug.Print "arrF(12) is " & arrF(12)[/blue]
[/code]
What is [tt][Factor][/tt] defined as in your data base? String? Number? Date?

Have fun.

---- Andy

 

[dhookom]

I would try use the UBound() function in a loop to build the "IN" clause. There is a function Convert array to delimited string that might help.

As Andy suggests it is important understand the difference between data types.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

I concur with Duane.

This is untested but maybe something like:

inclause= 0
Items = UBound(arrF)
For x = 0 To Items
if len(arrf(x))>0 then 
inclause= inclause & arrf(x) & ","
end if
next
If Right(inclause, 1) = "," Then
inclause = Left(inclause, Len(inclause)-1)
End If
...
...
strSQL = "SELECT * FROM " & strTbl & " WHERE " & strTbl & ".[Factor] IN(" & inclause & ")"

Assumes a numeric data type which as Andy points out is a problem if it it text data or dates or something.

 

[PHV]

Why not simply this ?

strSQL = "SELECT * FROM " & strTbl & " WHERE Factor IN(" & Join(arrF, ",") & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Why not"
probably because some of us knew about the Split() function but didn't know there was there was an UnSplit().

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

I should have mentioned that all values of arrF() are integers. Sorry.

 

[dhookom]

VickyC,
Did any of the posting in this thread help? Inquirying minds want to know.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

Why not.."

Didn't even know JOIN existed. Wow. I love diplaying my ignorance.

 

[Gammachaser]

If the only cost of an education is embarrassment, it comes cheap.