Strange SELECT query behaviour

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

Hello. I am trying to provide a criteria for a SELECT query. My user will
select multiple items from a listbox. I grab the selected items and store
them in an array. At that point I have created a function which collects all
the array elements and passes them to the query. However the qyery returns
nothing. If I type the values in manually it works

So my function looks like this: In("H2","H3","H4")

and nothing is returned. However typing the criteria like that will work

What's happening? Can anyone help?

Thanks a lot

L.
 
What is the function's code? What is the query that you're using? What are
the data like? What are your table structures?

We don't have enough information to make any suggestions....
 
Ken, thanks. Here's the code I'm using


Public Function SelectedFunds() As String
Dim LowerVal As Variant
Dim UpperVal As Variant
Dim i As Integer
Dim str1 As String
Dim arr2() As String

'Lower Upper boundry
LowerVal = LBound(arrSelectedFunds)
UpperVal = UBound(arrSelectedFunds)


For i = LowerVal To UpperVal

If arrSelectedFunds(i) <> "" Then

If str1 <> "" Then

str1 = str1 & " Or '" & arrSelectedFunds(i) & "'"

Else

str1 = "'" & arrSelectedFunds(i) & "'"

End If

End If

Next

SelectedFunds = str1

End Function

The problem is that the string will *wrap* the string within in double
quotes, so for example" str1 will look like: "'H61B' Or 'H61C' Or 'H61D'"

Replace is not working. What else can I do??

Thanks a lot
 
You're trying to use invalid SQL syntax in your "WHERE" clause. If you want
to match a field on more than one value, the WHERE clause would look like
this:

WHERE MyField = 'H61B' Or MyField = 'H61C' Or MyField = 'H61D'

Note that the field name must be used for each comparison. Alternatively,
you can do this:

WHERE MyField In ('H61B','H61C','H61D')


Change your code to produce one of the above syntaces and see if that works
for you.
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top