Report Parameter On DoCmd

[d9pierce1]

Hi all,
I need some assistance with this code. Not really sure how to word it but here goes
I got the date parameters working on just the form but i need to put the same code in above code so it works with selected items.


I need to include the green code into the red code, (See Code) and I cant figure out how?

   With Me.LstCategoryReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
    Next
    End With


If Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[CategoryID] in(" & getLBX(Me.LstCategory) & ")"


ElseIf Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstCategorySub.Column(0)) Then
    DoCmd.OpenReport strDoc, acViewPreview, , "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ")"


Else
        DoCmd.OpenReport strDoc, acViewPreview, "", Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, acNormal
End If

Thanks
Dave

[Minty]

Put your filter/where criteria into a variable then debug.print it to see how it constructs.

Once you get that right open the report and use the variable you constructed, something like

Dim sCriteria as String




If Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) Then
    
	sCriteria = "[CategoryID] in(" & getLBX(Me.LstCategory) & ")"


ElseIf Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstCategorySub.Column(0)) Then


    sCriteria = "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ")"


Else
     
	 sCriteria = Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter 


End If

Debug.Print sCriteria


DoCmd.OpenReport strDoc, acViewPreview, , sCriteria

[d9pierce1]

Hi Minty.
Thank you for the help. I have been working with this but the problem is it does not put the txtReportFilter with the Where Condition in the If and ElseIf
There should be a way to have both of these in the where conditions?

sCriteria = "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ")" AND Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter

[Minty]

Assuming your txtReport filter has another condition in it (something like "fldPerson = 'Fred' " ) it should be;

sCriteria = "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ") AND " & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter

Note the difference in positioning of the quote to get the AND into the string.

A Debug.Print should show you that.

[d9pierce1]

Hi Minty,
Thank you, that works like a charm!
I have a similiar situation where I need to add to the WhereCondition the Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter
Code Below, Where Condition in RED, How do I concat that?

Private Sub CmdReport_Click()
On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   http://allenbrowne.com
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    If IsNull(Me.LstAccountReport.Column(0)) Then
        MsgBox "You must select a Report from Reprot List!"
    Exit Sub
    End If
    
    
    'strDelim = """"            'Delimiter appropriate to field type. See note 1.
     With Me.LstAccountReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
       'now open the report
       'DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip


         DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere, OpenArgs:=strDescrip


       
    Next
 End With


    'Loop through the ItemsSelected in the list box.
    With Me.LstAccountType
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[AccountTypeID] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "AccountType: " & Left$(strDescrip, lngLen)
        End If
    End If
    
    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    
    'Omit the last argument for Access 2000 and earlier. See note 4.
    'DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    
        DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere, OpenArgs:=strDescrip


Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
 
 
End Sub

[Minty]

If it has the full where clause correctly specified simply set strWhere to it

   strWhere = Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter
   
   Debug.print strWhere
   DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere, OpenArgs:=strDescrip

[d9pierce1]

Ok, I got this to work (See Below) except when I select an AccountType, then I get above error

DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, OpenArgs:=strDescrip

[Minty]

You are missing an AND between the two criteria

AccountTypeID In (1) AND TransDate Between ....

[d9pierce1]

Hi,
I got this code to work except for when I hit an Account Type I get that same error above in post #7

Where in this code would I put the AND at? I cant figure it out.

Private Sub CmdReport_Click()




On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   http://allenbrowne.com
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    If IsNull(Me.LstAccountReport.Column(0)) Then
        MsgBox "You must select a Report from Reprot List!"
    Exit Sub
    End If
    
    'strDelim = """"            'Delimiter appropriate to field type. See note 1.
     With Me.LstAccountReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
       'now open the report
         DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, OpenArgs:=strDescrip


       
    Next
 End With


    'Loop through the ItemsSelected in the list box.
    With Me.LstAccountType
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 0
    If lngLen > 0 Then
        strWhere = "[AccountTypeID] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 0
        If lngLen > 0 Then
            strDescrip = "AccountType: " & Left$(strDescrip, lngLen)
        End If
    End If
    
    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    
    'Omit the last argument for Access 2000 and earlier. See note 4.
    'DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    
        DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, OpenArgs:=strDescrip


Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "CmdReport_Click"
    End If
    Resume Exit_Handler
 
 
End Sub

[d9pierce1]

Hi all,
I think i may know where the issues lie here? This is a Value list in the Account Type
Not sure how to accurately call on that in the code!

[Minty]

Debug.Print is your friend here. Comment out your error handler while you are debugging, and you can just go to the immediate window and hover over the variables in the VBA window.

Put the entire string together, and then debug.print it, before passing the variable to the

strWhere = strWhere & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter
Debug.Print strWhere

If you can't see what to do with it at that point, paste back what it's showing you in the immediate window and someone will be able to assist.

[d9pierce1]

Hi all,
Sorry for delay
I rran this several times with different senarios and this is results below. I know all works great except for when i select an from the account type list box, then i get an error!
If I select dates on my form, it filters it just nicely except when I select an account type from the list, I get the error? I have tried this 100 times different ways and just dont understand this.


?strWhere = strWhere & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter
False
True
True
False
False
[AccountTypeID] IN (1)
False[AccountTypeID] IN (1)
[AccountTypeID] IN (1)

[Minty]

You need to learn how to debug things in a more logical fashion.

This will hopefully give you some pointers.

Private Sub CmdReport_Click()


'On Error GoTo Err_Handler  '''' Commented out while you are Debugging things
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   http://allenbrowne.com
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    If IsNull(Me.LstAccountReport.Column(0)) Then
        MsgBox "You must select a Report from Reprot List!"
    Exit Sub
    End If
    
    'strDelim = """"            'Delimiter appropriate to field type. See note 1.
     With Me.LstAccountReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
       'now open the report
       ''' Removed the strWhere as not used in this process
         DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=  Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, OpenArgs:=strDescrip
        Next
    End With

    'Loop through the ItemsSelected in the list box.
    With Me.LstAccountType
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
    Debug.Print "First Debug " & strWhere ''' This will show you  what you have at this point.
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 0
    If lngLen > 0 Then
        strWhere = "[AccountTypeID] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 0
        If lngLen > 0 Then
            strDescrip = "AccountType: " & Left$(strDescrip, lngLen)
        End If
    End If
    
    Debug.Print "2nd Debug " & strWhere ''' This will show you  what you have at this point.
    
    
    'Report will not filter if open, so close it. For Access 97, see note 3.
    
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    
    'Omit the last argument for Access 2000 and earlier. See note 4.
    'DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    
    strWhere =  strWhere & " AND "  & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter 
    
    Debug.Print "3rd Debug " & strWhere  ''''' Final debug showing the criteria as passed to the report
    
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere  , OpenArgs:=strDescrip


Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "CmdReport_Click"
    End If
    Resume Exit_Handler
 
 
End Sub

Run the code then look at the immediate window.

[d9pierce1]

I have two different scenarios here that I am dealing with.
The first one is:
If I have a date in the code works fine when selecting from the list boxes. If I dont have a date then it gives me a syntax error?
Is there maybe a way of an If in that code? See below.

Private Sub CmdReport_Click()


Dim varItem As Variant
Dim strDoc As String
Dim sCriteria As String


If IsNull(Me.LstCategoryReport.Column(0)) Then
    MsgBox "You must select a Report from Reprot List!"
    Exit Sub
End If


On Error GoTo Err_Handler


   With Me.LstCategoryReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
    Next
    End With


If Not IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) Then
    sCriteria = "[CategoryID] in(" & getLBX(Me.LstCategory) & ")AND " & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter
    
ElseIf Not IsNull(Me.LstCategory.Column(0)) And Not IsNull(Me.LstCategorySub.Column(0)) Then
    sCriteria = "[SubCategoryID] in(" & getLBX(Me.LstCategorySub) & ")AND " & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter
    
Else
        sCriteria = Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter
End If


Debug.Print sCriteria




DoCmd.OpenReport strDoc, acViewPreview, , sCriteria




Exit_Handler:
    Exit Sub


Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
 
End Sub

The second issue is:
If I dont have a date the code works well, if I put in the between dates then it gives me a syntax error. Oposit of the above!
See code below:

Private Sub CmdReport_Click()


On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   http://allenbrowne.com
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    If IsNull(Me.LstAccountReport.Column(0)) Then
        MsgBox "You must select a Report from Reprot List!"
    Exit Sub
    End If
    
    'strDelim = """"            'Delimiter appropriate to field type. See note 1.
     With Me.LstAccountReport
        For Each varItem In .ItemsSelected
           strDoc = .Column(1, varItem) 'assumes list box has two columns, the first one holding the reportID is the bound one and hidden
       'now open the report
         DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, OpenArgs:=strDescrip
    Next
 End With
    'Loop through the ItemsSelected in the list box.
    With Me.LstAccountType
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 0
    If lngLen > 0 Then
        strWhere = "[AccountTypeID] IN(" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 0
        If lngLen > 0 Then
            strDescrip = "AccountType: " & Left$(strDescrip, lngLen)
            
        End If
    End If
    
    'Report will not filter if open, so close it. For Access 97, see note 3.
    If CurrentProject.AllReports(strDoc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
    
    'Omit the last argument for Access 2000 and earlier. See note 4.
    'DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    
        DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, OpenArgs:=strDescrip
Debug.Print strWhere


Exit_Handler:
    Exit Sub




Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "CmdReport_Click"
    End If
    Resume Exit_Handler
    
End Sub

The report filter is this:
Group filter options are
1 "Show betweek these dates
2 "Dont apply filter

=Choose([grpFilterOptions],"[TransDate] Between #" & [Forms]![frmMainMenu].[sfrmMainMenu].[Form].[BeginningTransDate] & "# AND #" & [Forms]![frmMainMenu].[sfrmMainMenu].[Form].[EndingTransDate] & "#","")

[Minty]

You will need to check for the presence of one or other of the criteria and build the where clause appropriately.
Break it down into separate logical pieces which you sort of started to do then went all rogue on us.

In steps you need to do the following - not real code but you should get the drift.

Is there a date filter = No  then 
strDateFilter = ""
Else 
strDateFilter = "TransDate Between #2023-03-01#  AND #2023-04-01#"  
end if

Is there some other filter Yes 
then strClientFilter = "Client = 'Fred'"
Else strClientFilter = ""
end if

Now add them together

Is there a date filter = No then 
strFinalCriteria = strclientfilter
Else  
strFinalCriteria = strDatefilter & " AND " & strClientFilter
end if

Finally check what you have makes sense
Debug.Print strFinalCriteria

This is not the most elegant way to do it but is simple and easy to follow.