Code to use start & end date entries and cbo selected item to open the related report (1 Viewer)

Punice

Registered User.
Local time
Today, 07:55
Joined
May 10, 2010
Messages
135
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:55
Joined
Aug 30, 2003
Messages
36,118
Re: Code to use start & end date entries and cbo selected item to open the related re

Looking at that code, I'd assume that the different reports do the filtering for expense, as there is none in the code. Is that not the case? If the only difference in the reports is the filtering, I'd have a single report and add the expense filtering to the date filter.

FYI, I deleted your duplicate thread. Please don't post the same question twice.
 

Punice

Registered User.
Local time
Today, 07:55
Joined
May 10, 2010
Messages
135
Re: Code to use start & end date entries and cbo selected item to open the related re

Yes, I have 16 different reports that match the combobox item selections. Filtering is done within individual reports. The problem is that, for the example code, only the start & end dates are filtered and not the combobox item selected, that should direct which report to open (eg., "Advertising" to open "rptExp_AD" for the date range).

Sorry about the duplication. I thought I erased the one in 'forms'.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:55
Joined
Aug 30, 2003
Messages
36,118
Re: Code to use start & end date entries and cbo selected item to open the related re

Like I said, I'd have a single report. Are you saying that the test of the combo isn't working? Is it perhaps bound to an ID field instead of the text?
 

Punice

Registered User.
Local time
Today, 07:55
Joined
May 10, 2010
Messages
135
Re: Code to use start & end date entries and cbo selected item to open the related re

I solved my problem by writing 16 queries to do the sorting, using the cbo item selected as the query's criteria for each of my items, and by using the individual queries as the record source for the reports. Clunky, but works.

Thanks for you help.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:55
Joined
Aug 30, 2003
Messages
36,118
Re: Code to use start & end date entries and cbo selected item to open the related re

Glad you got it working. At the end of the day, you're the one that has to maintain it, so you need to do it your way.
 

Punice

Registered User.
Local time
Today, 07:55
Joined
May 10, 2010
Messages
135
Re: Code to use start & end date entries and cbo selected item to open the related re

You shamed me into doing it right. Here is the code that does what you suggested. Hopefully, it will help somebody else, who needs to do what I needed to do. Again, Thanks for the help and prodding.

Private Sub cboExpense_Selector_AfterUpdate()
Dim strWhere As String
Dim strReport As String
Dim strDateField As String
Dim strFilter As String
Dim strtxtTitle As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'Set the values in the next 3 lines.
strReport = "rptSelExp"
strWhere = "[IRS Class]=" & Chr(34) & Me.[cboExpense_Selector] & Chr(34)
strDateField = "E_Date"
lngView = acViewPreview

'Build the filter string.
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & IIf(strWhere = "", "", " AND ") & "[E_Date] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
End If

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & IIf(strWhere = "", "", " AND ") & "[E_Date] Between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
End If
'Open the report picked from the combobox dropdown list.
DoCmd.OpenReport strReport, lngView, , strWhere
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:55
Joined
Aug 30, 2003
Messages
36,118
Re: Code to use start & end date entries and cbo selected item to open the related re

I'd like to think of it as "encouraged", not "shamed". :p

Isn't it adding the date criteria twice if both textboxes are filled in?
 

MarkK

bit cruncher
Local time
Today, 04:55
Joined
Mar 17, 2004
Messages
8,178
Here's another way to write the same code with a bunch of snap-in parts, each doing a very narrow job.
Code:
Private Sub cboExpense_Selector_AfterUpdate()
    DoCmd.OpenReport "rptSelExp", acViewPreview, , Me.MyFilter
End Sub

Property Get MyFilter() As String
    MyFilter = Me.ClassFilter & Me.StartFilter & Me.EndFilter
End Property

Property Get ClassFilter() As String
    ClassFilter = "[IRS Class] = " & Chr(34) & Me.[cboExpense_Selector] & Chr(34) & " "
End Property

Property Get StartFilter() As String
    If IsDate(Me.txtStartDate) Then StartFilter = "AND [E_Date] >= #" & Me.txtStartDate & "# "
End Property

Property Get EndFilter() As String
    If IsDate(Me.txtEndDate) Then EndFilter = "AND [E_Date] <= #" & Me.txtEndDate & "# "
End Property
This approach leaves you with a bunch of filter functionality that you might be able to use again, later on. :)
 

Punice

Registered User.
Local time
Today, 07:55
Joined
May 10, 2010
Messages
135
Re: Code to use start & end date entries and cbo selected item to open the related re

My code work ok, except for when I enter a start date & leave the end date blank. However, I'll work on that problem.

I don't know if I have a 'double entry' problem, like you pointed out.

With respect to the other code provide, I tried it. It worked fine for all date entries, but failed when I closed & re-entered the form with the control that executes the sub-routine. I'll attempt to understand why.

Thanks, again for all of you wizards for your most appreciated assistance.
 

Users who are viewing this thread

Top Bottom