Filter report with Combobox AND Multiselect Listbox

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.
 
M

Marshall Barton

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.
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)
 
O

onlinegroups

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.

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 -

Thanks I will give this a try.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top