MultiSelect lstbox data in query

[Dan2kx]

Good eve peeps,

got a quicky for ya (hopefully) i have two multiselect listboxes on a form and i would like a query to show all the selected values from both boxes, i tried to sting the data first but no luck, one is a string value the other a long (but i tried to string this too)

lHolCom as String
lStaff as Long

any ideas? can it be done? am i gna have to run seperate queries into a table?

Dan

[DonRayner]

In the conditions for your query statement you can "or" the two listboxes.

Example:

SELECT Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1
WHERE (([Field1]=[Forms]![Form1]![ListBox1])) OR (([Field2]=[Forms]![Form1]![ListBox2]));

[Dan2kx]

My problem is with two different multiselect listboxes
users should select multiple items from both and i want all related records to be shown
for lack of a "better" way i have created this code:

On Error GoTo Trap
Dim msg As String, HolCom As String, SID As String, row As Variant, row2 As Variant
Dim tdef As TableDef, mySQL As String
    If IsNull(Me.cDept) Then msg = msg & " - Department" & vbCrLf
    If IsNull(Me.txtDateFrom) Then msg = msg & " - Date From" & vbCrLf
    If IsNull(Me.txtDateTo) Then msg = msg & " - Date To" & vbCrLf
    If Me.lHolCom.ItemsSelected.count = 0 Then msg = msg & " - Leave Comment/s" & vbCrLf
    If Me.lStaff.ItemsSelected.count = 0 Then msg = msg & " - Staff Members/s" & vbCrLf
    If Not msg = "" Then MsgBox "Please Complete the following fields..." & vbCrLf & msg, vbCritical, "Some Details Missing..." Else GoTo Main:
Exit Sub
 
Main:
Set tdef = CurrentDb.CreateTableDef(LogStaffID & "tblReports")
With tdef
    .Fields.Append .CreateField("Name", dbText)
    .Fields.Append .CreateField("Date", dbDate)
    .Fields.Append .CreateField("AM", dbInteger)
    .Fields.Append .CreateField("PM", dbInteger)
    .Fields.Append .CreateField("MinsUsed", dbInteger)
    .Fields.Append .CreateField("Comments", dbText)
    CurrentDb.TableDefs.Append tdef
End With
    DoCmd.SetWarnings False
    For Each row In Me.lHolCom.ItemsSelected
        For Each row2 In Me.lStaff.ItemsSelected
            DoCmd.RunSQL "INSERT INTO [" & LogStaffID & "tblReports] ( Name, [Date], AM, PM, MinsUsed, Comments ) SELECT [Surname] & ', ' & [Forename] AS Name, tbl_Holidays.Date, tbl_Holidays.AM, tbl_Holidays.PM, IIf([UseMins]=True,[MinutesUsed],0) AS MinsUsed, tbl_Holidays.Comments FROM tblStaff INNER JOIN tbl_Holidays ON tblStaff.StaffID = tbl_Holidays.StaffID WHERE (((tbl_Holidays.Date) Between " & Me.txtDateFrom & " And " & Me.txtDateTo & ") AND ((tbl_Holidays.Comments)='" & Me.lHolCom.ItemData(row) & "') AND ((tbl_Holidays.StaffID)=" & Me.lStaff.ItemData(row2) & ") AND ((tbl_Holidays.AuthBY) Is Not Null) AND ((tbl_Holidays.CancBY) Is Null)) ORDER BY tblStaff.Surname;"
        Next
    Next
    DoCmd.SetWarnings True
    DoCmd.OpenReport "rptReports", acViewDesign
    [Reports]![rptReports].RecordSource = LogStaffID & "tblReports"
    [Reports]![rptReports]![Label12].Caption = "Staff Leave Reports " & Me.txtDateFrom & " " & Me.txtDateTo
    DoCmd.OpenReport "rptReports", acViewPreview
Exit Sub
Trap:
    Select Case Err.Number
        Case 3010
            DoCmd.DeleteObject acTable, LogStaffID & "tblReports"
                Resume
        Case Else
            MsgBox Err.Number & " " & Err.Description
    End Select
 

Any "easier" solutions welcome

[DonRayner]

Dan, I'll take a look at it again in the morning. My thought is to set the query's criteria to a function and use the function to build up the where statement based on the listbox selections.

[ChipR]

If you can string together the values from the list box, you can easily use the SQL IN condition.

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)