I have a form (screenshot uploaded) which allows criteria to pull reports to show based on criteria from the following VBA code:
Option Compare Database
Private Sub cboReport_AfterUpdate()
If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")) = False Then
Me.cboCrit1.Visible = True
Me.cboCrit1.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
Me.lblCrit1.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
Me.cboCrit1.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit1""")
Else
Me.cboCrit1.Visible = False
End If
If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")) = False Then
Me.cboCrit2.Visible = True
Me.cboCrit2.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
Me.lblCrit2.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
Me.cboCrit2.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit2""")
Else
Me.cboCrit2.Visible = False
End If
If IsNull(DLookup("ControlID", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")) = False Then
Me.cboCrit3.Visible = True
Me.cboCrit3.RowSource = DLookup("ControlSql", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
Me.lblCrit3.Caption = DLookup("ControlCaption", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
Me.cboCrit3.Tag = DLookup("FieldName", "AppSysReportControls", "ReportID=""" & Me.cboReport & """ AND ControlID=""cboCrit3""")
Else
Me.cboCrit3.Visible = False
End If
Me.lblDetails.Caption = Me.cboReport.Column(2)
Me.cboPrintMethod = 1
End Sub
Private Sub cmdClearCrit_Click()
Me.cboCrit1.Value = ""
Me.cboCrit2.Value = ""
Me.cboCrit3.Value = ""
End Sub
Private Sub Ctl_cmdPrint_Click()
Dim strCrit As String
If Me.cboCrit1 <> "" Then
strCrit = Me.cboCrit1.Tag & "=""" & Me.cboCrit1.Value & """"
End If
If Me.cboCrit2 <> "" Then
If strCrit = "" Then
strCrit = Me.cboCrit2.Tag & "=#" & Me.cboCrit2 & "#"
Else
strCrit = strCrit & " AND " & Me.cboCrit2.Tag & "=#" & Me.cboCrit2.Value & "#"
End If
End If
If Me.cboCrit3 <> "" Then
If strCrit = "" Then
strCrit = Me.cboCrit3.Tag & "=#" & Me.cboCrit3 & "#"
Else
strCrit = strCrit & " AND " & Me.cboCrit3.Tag & "=#" & Me.cboCrit3.Value & "#"
End If
End If
If Me.cboPrintMethod = 1 Then
DoCmd.OpenReport Me.cboReport.Column(3), acViewPreview, , strCrit
Else
DoCmd.OpenReport Me.cboReport.Column(3), acViewNormal, , strCrit
End If
DoCmd.Close acForm, "frmDlgRpts"
End Sub
This code pulls Controlsql information to execute the criteria from the tables which are attached as screenshots. What I would like to do is pull a report by a date range entered into the criteria. The query that runs the report only has one date column TxnDate. I have attemped to use "build" to apply the setting to pull the criteria from the form but this does not work because the form must pull from the controlsql. Is there a formula I can enter into the control sql for this or what would the code be to add to the vba to omit the controlsql criteria for report 04?
Thank you for your help