filter query based on single column list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

I want to filter this query by the values in my single column list box. I
made this function to create the string, but the query doesn't like it. If I
paste the string that the function returns into the criteria line the query
works.

What am I doing wrong?

Thanks,

Brad

SQL:
SELECT tblPeripheral.Type, tblPeripheral.Location, tblPeripheral.[Serial
Number], tblPeripheral.Model, tblPeripheral.Brand, tblPeripheral.[Model 2],
ExportPeripheralInfo() AS Expr1
FROM tblPeripheral
WHERE (((tblPeripheral.Type)=ExportPeripheralInfo()));


Function:
Function ExportPeripheralInfo() As String
Dim FilterCriteria As String
Dim ctl As Control
Dim c As Variant
Set ctl = Forms![frmReportCenter]![lstSiteOrType]

For Each c In ctl.ItemsSelected
If FilterCriteria = "" Then
FilterCriteria = """" & ctl.ItemData(c) & """"
Else
FilterCriteria = """" & ctl.ItemData(c) & """" & " Or Like " &
FilterCriteria
End If
Next c

ExportPeripheralInfo = "Like " & FilterCriteria
'Debug.Print ExportPeripheralInfo
End Function
 
That is absoluely PERFECT!!!!

Thanks so much Duane.

Have a great weekend.

Brad

Duane Hookom said:
You can't use a function like this in a query. The "Or Like" just can't be
returned in the function. There is a generic multiselect listbox function
that you can use at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
Microsoft Access MVP


Brad said:
Thanks for taking the time to read my question.

I want to filter this query by the values in my single column list box. I
made this function to create the string, but the query doesn't like it. If I
paste the string that the function returns into the criteria line the query
works.

What am I doing wrong?

Thanks,

Brad

SQL:
SELECT tblPeripheral.Type, tblPeripheral.Location, tblPeripheral.[Serial
Number], tblPeripheral.Model, tblPeripheral.Brand, tblPeripheral.[Model 2],
ExportPeripheralInfo() AS Expr1
FROM tblPeripheral
WHERE (((tblPeripheral.Type)=ExportPeripheralInfo()));


Function:
Function ExportPeripheralInfo() As String
Dim FilterCriteria As String
Dim ctl As Control
Dim c As Variant
Set ctl = Forms![frmReportCenter]![lstSiteOrType]

For Each c In ctl.ItemsSelected
If FilterCriteria = "" Then
FilterCriteria = """" & ctl.ItemData(c) & """"
Else
FilterCriteria = """" & ctl.ItemData(c) & """" & " Or Like " &
FilterCriteria
End If
Next c

ExportPeripheralInfo = "Like " & FilterCriteria
'Debug.Print ExportPeripheralInfo
End Function
Click to expand...
Click to expand...
 
Back
Top