Report Parameter On DoCmd

[d9pierce1]

thank you,
Here are the results!
'Just opening Report, No Date, No select account type
Yellow... DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip Note:strWhere = "AND" ,strDescrip =""
First Debug
2nd Debug
3rd Debug AND




' Just open report with no dates and asset selecterd from account types list box
Yellow... DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip Note:strWhere = [AccountTypeID] IN (1) AND , OpenArgs=Null strDescrip = "AccountType: "Asset","
First Debug First Debug 1
2nd Debug [AccountTypeID] IN (1)
3rd Debug [AccountTypeID] IN (1) AND




'Just open report with Dates, Syntax Error
Yellow... DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip Note:strWhere = AND [TransDate] Between #4/1/2023# AND #4/12/2023# ,strDescrip =""
First Debug
2nd Debug
3rd Debug AND [TransDate] Between #4/1/2023# AND #4/12/2023#




'Just open report with dates and select Asset from account type, Opened report correctly
First Debug
2nd Debug
3rd Debug AND [TransDate] Between #4/1/2023# AND #4/12/2023#

[Minty]

So you can see from the Debug that your strCriteria isn't being formed correctly.
You are adding an AND where you don't need one either at the beginning or end of your statement, which is why it's failing. It has to be a valid Where clause.

You will need to use logic similar to what I described above to decide how to add the criteria together.

(I'm note sure what all the Note: stuff is in your command line, I assume it's something you are manually adding???)

[d9pierce1]

The note stuff is what is under the debug yellow highlight when hovered over.

I put this together (below) but the only other filter is the list box and not sure how to word it exactly?

Dim strDateFilter As String
Dim strAccountFilter As String
Dim strFinalCriteria As String


If Me.grpFilterOptions = 2 then
strDateFilter = ""
Else 
strDateFilter = txtReportFilter 
end if


'I am not real sure how to do this with a list box with value list?
'There are 5 values possible in this list box
'1;"Asset";2;"Expense";3;"Income";4;"Liability(ST)";5;"Liability(LT)"


If Me.LstAccountType = 1,2,3,4,5 Then
strAccountFilter = ""
Else
strAccountFilter = Me.LstAccountType.Column(0)?
End If


If Me.grpFilterOptions = 2 then
strFinalCriteria = strAccountFilter
Else  
strFinalCriteria = strDatefilter & " AND " & strAccountFilter
end if


Finally check what you have makes sense
Debug.Print strFinalCriteria

[Minty]

Is it a multi select list or single select? Assuming its a single item then I would do something like

If Me.LstAccountType = 0 Then 
      strAccountFilter = ""
Else
   strAccountFilter = "AccountTypeID = " & Me.LstAccountType
End If

By default the value for a listbox is column(0)

Remember you are building a Where clause - it needs to know what your criteria are for.

[d9pierce1]

Good news,
I was able to get the Category Reports to work by adding a If to the DoCmd Line

Private Sub CmdReport_Click()


Dim varItem As Variant
Dim strDoc 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.LstCategoryType.Column(0)) And IsNull(Me.LstCategory.Column(0)) And IsNull(Me.LstCategorySub.Column(0)) Then
    'DoCmd.OpenReport strDoc, acViewPreview, , "[CategoryTypeID] in(" & getLBX(Me.LstCategoryType) & ")"
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
    If Me.grpFilterOptions = 2 Then
        DoCmd.OpenReport strDoc, acViewPreview
    Else
        DoCmd.OpenReport strDoc, acViewPreview, , Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, acNormal
    End If
End If
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

[d9pierce1]

thanks Minty,
I will put that to good use and get thiss account reports figured out
Again,
Thanks for all the assistance

[d9pierce1]

Hi Minty
Just went thru the other one and was able to get it fixed too.
Thanks again for all the help!
Appriciate it

Dave

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:=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) - 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
    
    If Me.grpFilterOptions = 2 Then
        DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
    Else
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & " AND " & Forms![frmMainMenu].[sfrmMainMenu].Form.txtReportFilter, OpenArgs:=strDescrip
    End If




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]

Glad you got it sorted out.