Multi-select List box as query parameter

J

Judy Ward

I have a parameterized query (qrySAR_Status_by_IR) that I am passing a string
to through a form:
SELECT tblSAR.SAR_ID, tblSAR.SAR_Multipurpose
FROM tblSAR
WHERE (((tblSAR.SAR_Multipurpose) Like
[Forms]![frmMain]![txtBuildParameter]));

I am building the string through a multi-select list box on the form:
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "*" & ctl.ItemData(varItem) & "*" _
& " OR (tblSAR.SAR_Multipurpose) Like "
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, (Len(strSQL) - 35))
Me.txtBuildParameter = strSQL
DoCmd.OpenQuery "qrySAR_Status_by_IR"

This code works as expected with one selection, but returns nothing when two
or more items are selected. A Debug.Print on strSQL returns:
*IR-7* ' For one selection--and this works
*IR-7* OR (tblSAR.SAR_Multipurpose) Like *IR-8* 'For two selections--which
does not work

Any idea what I am doing wrong?
Thank you,
Judy
 
F

Frank

Try ...

strSQL = "tblSAR.SAR_Multipurpose Like *" ' 30 characters in strSQL to begin
with
For Each varItem In ctl.ItemsSelected
If Len(strSQL) = 30 Then
strSQL = strSQL & ctl.ItemData(varItem) & "*"
Else
strSQL = strSQL & " OR tblSAR.SAR_Multipurpose Like *" &
ctl.ItemData(varItem) & "*"
End If
Next varItem
Me.txtBuildParameter = strSQL
DoCmd.OpenQuery "qrySAR_Status_by_IR"


There are far more efficient and flexible ways to do what you're doing here
.... try this -

1.create a query called qryMyQuery containing anything you want
2.create a table called tblMyNumbers with one field intNumber
3.create 10 records in tblMyNumbers with the numbers 0 through 9 in intNumber
4.create a new module and copy the code below into it (make sure you're
using DAO)
5.run the code

Public Sub gsubTryIt()

Dim q As QueryDef

Set q = CodeDB.QueryDefs("qryMyQuery")
q.SQL = "SELECT * FROM tblMyNumbers WHERE intNumber > 4"
Set q = Nothing ' qryMyQuery will now have the SQL on the above line in it

DoCmd.OpenQuery "qryMyQuery"

End Sub
 
J

Judy Ward

I keep seeing "QueryDef" in code examples, but when I try to use it in the
code for a form (in the click event, for example):
Dim q As QueryDef

I get:
Compile error: User-defined type not defined.

I am using Access 2002. Where are you using the code example below?

Thank you,
Judy
 
F

Frank

To use QueryDef you need to reference DAO ... 'design' a module ... select
Tools > Referneces ... check Microsoft DAO 3.6 ... if you're using ADO you'll
need to sek out the corresponding functionality ... did you get you code
problem to work?
 
J

Judy Ward

I finally got a book out and read about ADO and DAO--neither one was assigned
to my database by default (and I didn't know I needed one or both). I was
going crazy trying to figure out why I couldn't declare a database or
querydef!

Thank you for responding, I've got things working now.
Judy
 
Top