Filter report with Combobox AND Multiselect Listbox

  • Thread starter Thread starter heidi
  • Start date Start date
H

heidi

Not sure if I should put this in forms or reports. But here it goes.

Form: frmRecommendationsProductSummary
controls:
1. ComboBox: cboLotnumber
2. Multiselect Listbox: lstAvailableBlocks
3. Text Boxes: txtStartDate and txtEndDate
3. Command Button: cmdPrint
Report: rptRecommendationsProductsNeeded based on query showing all
records


On the afterupdate of my cboLotnumber it requeries my
lstAvailableBlocks.

How do I filter the report with criteria from my combobox, textboxes
and multiple selections from my listbox. I have not done a filter
before where I combined the combobox and listbox for the filter. Do
now know where to begin to build the WHERE string.
 
heidi said:
Not sure if I should put this in forms or reports. But here it goes.

Form: frmRecommendationsProductSummary
controls:
1. ComboBox: cboLotnumber
2. Multiselect Listbox: lstAvailableBlocks
3. Text Boxes: txtStartDate and txtEndDate
3. Command Button: cmdPrint
Report: rptRecommendationsProductsNeeded based on query showing all
records


On the afterupdate of my cboLotnumber it requeries my
lstAvailableBlocks.

How do I filter the report with criteria from my combobox, textboxes
and multiple selections from my listbox. I have not done a filter
before where I combined the combobox and listbox for the filter. Do
now know where to begin to build the WHERE string.
Click to expand...
The general idea for the button's C;ocl event procedure is
to use an If block for each control. Something along these
lines:

' number type table fields
If Not IsNull(Me.thistextbox) Then
stWhere = stWhere & " And thisfield = " & Me.thistextbox
End If

' Text table fields
If Not IsNull(Me.thistextbox) Then
stWhere = stWhere & " And thisfield = """ &
Me.thistextbox & """"
End If

' Date type table fields
If Not IsNull(Me.thattextbox) Then
stWhere = stWhere & " And thatfield = " &
Format(Me.thattextbox, "\#yyyy-m-d\#)
End If

' multi-select list box of numbers
stIn = ""
For Each x In Me.somelistbox.ItemSelected
stIn = stIn & "," & Me.somelistbox.ItemData(x)
Next x
If Len(stIn) > 0 Then
stWhere = stWhere & " And somefield IN(" & Mid(stIn,2)
& ") "
End If

' multi-select list box of text strings
stIn = ""
For Each x In Me.otherlistbox.ItemSelected
stIn = stIn & ",""" & Me.otherlistbox.ItemData(x) & """"
Next x
If Len(stIn) > 0 Then
stWhere = stWhere & " And otherfield IN(" & Mid(stIn,2)
& ") "
End If

'MsgBox Mid(stWhere, 6)
DoCmd.OpenReport "name of report", acViewPreview, _
WhereCondition:= Mid(stWhere, 6)
 
heidi said:
Not sure if I should put this in forms or reports.  But here it goes.
Click to expand...
Form:  frmRecommendationsProductSummary
controls:
1.  ComboBox: cboLotnumber
2.  Multiselect Listbox: lstAvailableBlocks
3.  Text Boxes:  txtStartDate and txtEndDate
3.  Command Button:  cmdPrint
Report: rptRecommendationsProductsNeeded  based on query showing all
records
Click to expand...
On the afterupdate of my cboLotnumber it requeries my
lstAvailableBlocks.
Click to expand...
How do I filter the report with criteria from my combobox, textboxes
and multiple selections from my listbox.   I have not done a filter
before where I combined the combobox and listbox for the filter.  Do
now know where to begin to build the WHERE string.
Click to expand...

The general idea for the button's C;ocl event procedure is
to use an If block for each control.  Something along these
lines:

                                        ' number type table fields
        If Not IsNull(Me.thistextbox) Then
                stWhere = stWhere & " And thisfield =" & Me.thistextbox
        End If

                                        ' Text table fields
        If Not IsNull(Me.thistextbox) Then
                stWhere = stWhere & " And thisfield =""" &
Me.thistextbox & """"
        End If

                                        ' Date type table fields
        If Not IsNull(Me.thattextbox) Then
                stWhere = stWhere & " And thatfield =" &
Format(Me.thattextbox, "\#yyyy-m-d\#)
        End If

                                        ' multi-select list box of numbers
        stIn = ""
        For Each x In Me.somelistbox.ItemSelected
                stIn = stIn & "," & Me.somelistbox.ItemData(x)
        Next x
        If Len(stIn) > 0 Then
                stWhere = stWhere & " And somefield IN(" & Mid(stIn,2)
& ") "
        End If

                                        ' multi-select list box of text strings
        stIn = ""
        For Each x In Me.otherlistbox.ItemSelected
                stIn = stIn & ",""" & Me.otherlistbox.ItemData(x) & """"
        Next x
        If Len(stIn) > 0 Then
                stWhere = stWhere & " And otherfield IN(" & Mid(stIn,2)
& ") "
        End If

        'MsgBox Mid(stWhere, 6)
        DoCmd.OpenReport "name of report", acViewPreview, _
                                                                                       WhereCondition:= Mid(stWhere, 6)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks I will give this a try.
 
Back
Top