Code to open report with 2 multi list boxes and data range

[Chris1963]

Hi,

I want to open my report via a Dialog box.
For the open report button I'm using the code that Alan Browne proposed.
I'm having a data range and 2 list boxes that I want to use, but I don't know how to adapt the code to build the string.
The filter works fine with 1 listbox lstAuthor, but when I also want to build de code for lstPublicationType.
I know that I have to put the strWhere4 somewhere in the where condition, but I don't know how to.
Or maybe someone has another idea?

Can anyone help me out?

Here is my code:

Private Sub cmdOpenReport_Click()
 'On Error Goto Err_Handler
 Dim strReport As String 'Name of report to open.
 Dim strField As String 'Name of your date field.
 Dim strWhere1 As String 'Where condition for OpenReport.
 Dim varItem As Variant 'Selected items
 Dim strWhere2 As String 'String to use as WhereCondition
 Dim strWhere3 As String
 Dim strWhere4 As String
Dim strDelim As String 'Delimiter for this field type.
 Const conDateFormat = "\#mm\/dd\/yyyy\#"
 strReport = "rptPublications"
 strField = "PublicationDate"
 If IsNull(Me.txtStartdate) Then
 If Not IsNull(Me.txtEnddate) Then 'End date, but no start.
 strWhere1 = strField & " <= " & _
 Format(Me.txtEnddate, conDateFormat)
 End If
 Else
 If IsNull(Me.txtEnddate) Then 'Start date, but no End.
 strWhere1 = strField & " >= " & _
 Format(Me.txtStartdate, conDateFormat)
 Else 'Both start and end dates.
 strWhere1 = strField & " Between " & _
 Format(Me.txtStartdate, conDateFormat) & _
 " And " & Format(Me.txtEnddate, conDateFormat)
 End If
 End If
 'strDelim = """"
 'Loop through the ItemsSelected in the list box.
 With Me.lstAuthor
 For Each varItem In .ItemsSelected
 If Not IsNull(varItem) Then
 strWhere2 = strWhere2 & strDelim & _
 .ItemData(varItem) & strDelim & ","
 End If
 Next
 End With
 'Remove trailing comma. Add field name, IN operator, and brackets.
 lngLen = Len(strWhere2) - 1
 If lngLen > 0 Then
 strWhere2 = "[WritersID] IN (" & Left$(strWhere2, lngLen) & ")"
 End If
  With Me.lstPublication_type
  For Each varItm In .ItemsSelected
  If Not IsNull(varItm) Then
  'Build up the filter from the bound column (hidden).
  strWhere4 = strWhere4 & strDelim & .ItemData(varItm) & strDelim & ","
  End If
  Next
  End With

  'Remove trailing comma. Add field name, IN operator, and brackets.
  lngLen = Len(strWhere4) - 1
  If lngLen > 0 Then
  strWhere4 = "[PublicationTypeID] IN (" & Left$(strThere, lngLen) & ")"
  End If

 If strWhere1 = "" Then
 strWhere3 = strWhere2
 ElseIf strWhere2 = "" Then
 strWhere3 = strWhere1
 Else
 strWhere3 = strWhere1 & " AND " & strWhere2
 End If
 'Debug.Print strWhere3
 DoCmd.OpenReport strReport, acViewPreview, , strWhere3
 Exit Sub
 Err_Handler:
 If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
 MsgBox "Error " & Err.Number & " - " & Err.Description, , "ok_Click"
 End If
 End Sub

[ranman256]

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,4)

  'just use the filter

me.filter = sWhere
me.filterOn = true

   'OR   
   'apply the sql to the form

sSql = "SELECT * FROM tblCompany WHERE " & sWhere

'save the sql as a qry or open the sql

[ranman256]

whrong note he3re