Code to use start & end date entries and cbo selected item to open the related report
I have a form with a startdate & enddate fields and a cboExpense_Selector. Need to fill start & end date fields & pick an expense item from the cbo and open a report that desplays the expense for that item for the date range entered.
My vba performs the range execution ok in the report, but the report contains all of the expenses from the tblExpenses.
I read & tried various 'solutions', but not the correct one. So, I'm submitting my code here, hoping that (once again) one of you Access 2007 wizards will solve my specific problem.
Here is enough code to use to evaluate my problem:
Private Sub cboExpense_Selector_AfterUpdate()
Dim strReport1 As String
Dim strReport2 As String
Dim strReport3 As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
'DO set the values in the next 3 lines.
strReport1 = "rptExp_AD"
strReport2 = "rptExp_OT"
strReport3 = "rptExp_VE"
strDateField = "E_Date"
lngView = acViewPreview
'Build the filter string.
If _IsDate(Me.txtStartDate) Then
strWhere = strDateField & ">= " & Format(Me.txtStartDate,_ strcJetDate)
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & strDateField & "<" & Format_(Me.txtEndDate + 1, strcJetDate)
End If
'Open the report picked from the combobox dropdown list.
If cboExpense_Selector.Value = "Advertising" Then 'AD
DoCmd.OpenReport strReport1, lngView, , strWhere
ElseIf cboExpense_Selector.Value = "Bank Charges" Then 'OT
DoCmd.OpenReport strReport2, lngView, , strWhere
ElseIf cboExpense_Selector.Value = "Biz Vehicle" Then 'VE
DoCmd.OpenReport strReport3, lngView, , strWhere
End If
Exit Sub
I have a form with a startdate & enddate fields and a cboExpense_Selector. Need to fill start & end date fields & pick an expense item from the cbo and open a report that desplays the expense for that item for the date range entered.
My vba performs the range execution ok in the report, but the report contains all of the expenses from the tblExpenses.
I read & tried various 'solutions', but not the correct one. So, I'm submitting my code here, hoping that (once again) one of you Access 2007 wizards will solve my specific problem.
Here is enough code to use to evaluate my problem:
Private Sub cboExpense_Selector_AfterUpdate()
Dim strReport1 As String
Dim strReport2 As String
Dim strReport3 As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
'DO set the values in the next 3 lines.
strReport1 = "rptExp_AD"
strReport2 = "rptExp_OT"
strReport3 = "rptExp_VE"
strDateField = "E_Date"
lngView = acViewPreview
'Build the filter string.
If _IsDate(Me.txtStartDate) Then
strWhere = strDateField & ">= " & Format(Me.txtStartDate,_ strcJetDate)
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & strDateField & "<" & Format_(Me.txtEndDate + 1, strcJetDate)
End If
'Open the report picked from the combobox dropdown list.
If cboExpense_Selector.Value = "Advertising" Then 'AD
DoCmd.OpenReport strReport1, lngView, , strWhere
ElseIf cboExpense_Selector.Value = "Bank Charges" Then 'OT
DoCmd.OpenReport strReport2, lngView, , strWhere
ElseIf cboExpense_Selector.Value = "Biz Vehicle" Then 'VE
DoCmd.OpenReport strReport3, lngView, , strWhere
End If
Exit Sub