Passing parameters to a query

[Hugh Janus]

Hi all,

I am stuck with this (probably simple) problem.

I have a form where I select values from several drop downs to then
pass to a query. This works great. However, now I've added a list
box and I want to pass the values selected from the listbox to filter
further with the query. I want the listbox values to be "OR", i.e.
any of the values selected will be displayed rather than a record that
has all the values. i.e.

The listbox contains:
Bob
John
Susan
Peter

I choose Bob and Peter. So, I want a query which shows the records
containing either Bob or Peter.

I thought I could build a string to then pass to the query but this
does not work. My code looks like this:

Private Sub Supportedon_Click()

Dim ctl As Control
Dim varItem As Variant
Dim strMatch As String

Set ctl = Me.Supportedon

If ctl.ItemsSelected.Count > 0 Then

strMatch = Chr(34) & " OR " & Chr(34) ' = Chr(34) & " " &
Chr(34) & " OR chr(34)" & Chr(34)

For Each varItem In ctl.ItemsSelected

strMatch = strMatch & ctl.ItemData(varItem) & Chr(34) & "
OR " & Chr(34)

Next varItem

If Strings.Left(strMatch, 7) = " " & Chr(34) & " OR " &
Chr(34) Then strMatch = Strings.Right(strMatch, Strings.Len(strMatch)
- 6)
If Strings.Right(strMatch, 6) = Chr(34) & " OR " & Chr(34)
Then strMatch = Strings.Left(strMatch, Strings.Len(strMatch) - 5)

Me.SupportedSTR.Caption = strMatch

End If

End Sub



I am sure that I have plenty of mistakes in the above because I have
spent hours changing it around and stabbing in the dark with it! :-
( I thought I could build a string that said "Bob" OR "Peter" and
then pass it but the query is always empty. If I add "Bob" OR "Peter"
directly into the query via design view it works, thus telling me that
the data is there but the query/form/code is wrong.

Please help!


TIA,
Hugh

 

[Brendan Reynolds]

It looks like you might be trying to build a SQL statement that looks
something like this ...

WHERE SomeFieldName = 'Bob' OR 'Peter'

This won't work, you need to include the field name each time, for example
....

WHERE SomeFieldName = 'Bob' OR SomeFieldName = 'Peter'

Try adding "Bob OR Peter" in design view, as you described, and then switch
to SQL view (select SQL View from the View menu) to see the SQL that the
query designer generates. It will probably become clearer then.

 

[Roger Carlson]

If you want a sample to look at, on my website (www.rogersaccesslibrary.com)
I have one called "CreateQueries2.mdb" which illustrates how to do this.
Look at Form 6.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Hugh Janus]

It looks like you might be trying to build a SQL statement that looks
something like this ...

WHERE SomeFieldName = 'Bob' OR 'Peter'

This won't work, you need to include the field name each time, for example
...

WHERE SomeFieldName = 'Bob' OR SomeFieldName = 'Peter'

Try adding "Bob OR Peter" in design view, as you described, and then switch
to SQL view (select SQL View from the View menu) to see the SQL that the
query designer generates. It will probably become clearer then.

Ah, now I understand. So, how can I adapt my code to provide for
this? When the query is taking the value from the textbox of the form
it is passing the whole lot as a string, thus failing. Somehow I need
to tell it that it is not a string but a SQL statement or something
similar. Any ideas?

Thanks,
Hugh