Use form to open and filter report

A

auntielo

Registered User.
Local time
Yesterday, 18:50
Joined
Apr 8, 2017
Messages
20
I have a Report with a RequestDate field. I am using a form to filter and it got a little complicated. I have two comboboxes, one to pick which field I want to filter, and one to choose the filter value. I (finally) got almost all of them working, but I am stuck on four: This Week, Next Week, Last Week, Before Last Week.

Everything I find is using two date fields and I can't seem to get Between or <> to work in code view. It makes sense, the txt box is supposed to hold a date not a formula, but can I do something that will allow me to filter that way?

Here is what is working so far for the other options:

Private Sub cboReportField_AfterUpdate()

Me.Filterby.RowSource = "SELECT Filterby, TableField FROM FieldFilterOptions WHERE [TableField] = '" & Me.cboReportField & "' ORDER BY [Filterby]"

End Sub

Private Sub Filterby_AfterUpdate()

If Me.Filterby.Value = "Today" Then
Me.DateFilter = Date

ElseIf Me.Filterby.Value = "Tomorrow" Then
Me.DateFilter = Date + 1

ElseIf Me.Filterby.Value = "Yesterday" Then
Me.DateFilter = Date - 1

End If

End Sub


Private Sub Command153_Click()

Dim strFilter As String

Select Case Me.cboReportField.Value

Case "Priority"

strFilter = "[Priority] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter


Case "TaskCategory"

strFilter = "[TaskCategory] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "TaskDescription"

strFilter = "[TaskDescription] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "ReportNeeded"

strFilter = "[ReportNeeded] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "DueDate"

strFilter = "[DueDate]=" & "#" & Me![DateFilter] & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "RequestDate"

strFilter = "[RequestDate]=" & "#" & Me![DateFilter] & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

End Select

End Sub
 
I don't know if that made sense, so I am making a second effort at explaining :)

This form is really basic, the main form that takes you to is more complicated, but it was making me cross-eyed.

cbo1: cboReportField
cbo2: Filterby
txt1: DateFilter
cmd1: Command153

When I select a value for cbo1 it limits the options for cbo2 to the corresponding values I can filter by. cmd1 opens and filters the report. I had to add a textbox to get the date ones to work. So, if cbo2 is Today, Tomorrow, Yesterday, txt1 is populated and cmd1 opens the report and filters correctly.

How can I do this for This Week, Next Week, Last Week, Before Last Week? Do I need 2 more textboxes with like startdate enddate? And then how do I write it?
 
New wrinkle. I was searching for a way to move all completed tasks to a different table and I see the best way is to filter. So I need to add a filter to only get records that are unchecked.
 
Another novice here, but.....
You can use Between for dates where the start and end are the same .

So use the same syntax for your single dates

Between Date-1 and Date-1
I would use StartDate and EndDate and set accordingly?

Also you can get rid of a lot of your code to make it easier to read and understand. Any time your are repeating the same code, your logic needs to be reviewed?

Also curious to know why you use elseifs in one sub and Case in another?

Code tags help a lot as well?
Code: 
Private Sub cboReportField_AfterUpdate()

Me.Filterby.RowSource = "SELECT Filterby, TableField FROM FieldFilterOptions WHERE [TableField] = '" & Me.cboReportField & "' ORDER BY [Filterby]"

End Sub

Private Sub Filterby_AfterUpdate()

If Me.Filterby.Value = "Today" Then
    Me.DateFilter = Date

ElseIf Me.Filterby.Value = "Tomorrow" Then
    Me.DateFilter = Date + 1

ElseIf Me.Filterby.Value = "Yesterday" Then
    Me.DateFilter = Date - 1
End If

End Sub


Private Sub Command153_Click()

Dim strFilter As String

Select Case Me.cboReportField.Value
Case "Priority"
    strFilter = "[Priority] =" & "'" & Me.Filterby & "'"
Case "TaskCategory"
    strFilter = "[TaskCategory] =" & "'" & Me.Filterby & "'"
Case "TaskDescription"
    strFilter = "[TaskDescription] =" & "'" & Me.Filterby & "'"
Case "ReportNeeded"
    strFilter = "[ReportNeeded] =" & "'" & Me.Filterby & "'"
Case "DueDate"
    strFilter = "[DueDate]=" & "#" & Me![DateFilter] & "#"
Case "RequestDate"
    strFilter = "[RequestDate]=" & "#" & Me![DateFilter] & "#"
End Select
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

End Sub

auntielo said:
I don't know if that made sense, so I am making a second effort at explaining :)

This form is really basic, the main form that takes you to is more complicated, but it was making me cross-eyed.

cbo1: cboReportField
cbo2: Filterby
txt1: DateFilter
cmd1: Command153

When I select a value for cbo1 it limits the options for cbo2 to the corresponding values I can filter by. cmd1 opens and filters the report. I had to add a textbox to get the date ones to work. So, if cbo2 is Today, Tomorrow, Yesterday, txt1 is populated and cmd1 opens the report and filters correctly.

How can I do this for This Week, Next Week, Last Week, Before Last Week? Do I need 2 more textboxes with like startdate enddate? And then how do I write it?
Click to expand...
 
Thanks!

You can use Between for dates where the start and end are the same.
So use the same syntax for your single dates
Between Date-1 and Date-1
I would use StartDate and EndDate and set accordingly?

What would that look like? I tried it multiple times and I always got an error. I will try it again and so I can tell you what it was.

Also you can get rid of a lot of your code to make it easier to read and understand. Any time your are repeating the same code, your logic needs to be reviewed?
Also curious to know why you use elseifs in one sub and Case in another?


This one is an easy answer :) I have been googling as I go. I had to make my questions as general as possible, so I would get pieces and then jury rig them together.

I am still trying to understand VBA logic. I am getting better, sort of, but I don't really know the foundation. Right now I am just discovering patterns and mimicking them. I love Boolean, so I imagine it will come eventually!

Code tags help a lot as well?
Whats a code tag?
 
auntielo said:
Thanks!

You can use Between for dates where the start and end are the same.
So use the same syntax for your single dates
Between Date-1 and Date-1
I would use StartDate and EndDate and set accordingly?

What would that look like? I tried it multiple times and I always got an error. I will try it again and so I can tell you what it was.
Click to expand...

I would Dim StartDate and EndDate then set them accordingly in each branch of the Case statements and the strFilter.

auntielo said:
Also you can get rid of a lot of your code to make it easier to read and understand. Any time your are repeating the same code, your logic needs to be reviewed?
Also curious to know why you use elseifs in one sub and Case in another?


This one is an easy answer :) I have been googling as I go. I had to make my questions as general as possible, so I would get pieces and then jury rig them together.
Click to expand...

Yes, I have done that with long periods between creating one piece of code and another. in the meantime I have found a better way of doing something.
I took it that this code was created in the same period?

auntielo said:
I am still trying to understand VBA logic. I am getting better, sort of, but I don't really know the foundation. Right now I am just discovering patterns and mimicking them. I love Boolean, so I imagine it will come eventually!
Click to expand...

Not a criticism, just an observation.

auntielo said:
Code tags help a lot as well?
Whats a code tag?
Click to expand...

When you post there is a # character in the toolbar. If you use that and post your code between the tags that generates then it looks as per my post as it surrounds the code with tags which keeps the indentation and has a different background.
 
As an aside, does that code actually work? as I read it as
Code: 
strFilter = "[Priority] =" & 'Today'
 
hahaha, I was wondering how people do that!

No criticism taken, it's a horrible way to do it. But now I am so far in I need to finish this before I can dig into VBA properly. I was complaining about my 140 column spreadsheet in February and someone pointed me to access. First I had ever heard of it!

As for when - kinda sorta. I only get so much time to work on it. I will remember a code I used before and paste it in, then find the extra portion.

Thanks for the clean up, if I was uncertain, I would always add extra to be safe. I did that a lot with ( ) in Boolean. If one set is good, 3 must be better.... well, at least 3 won't hurt.... maybe
 
Gasman said:
As an aside, does that code actually work? as I read it as
Code: 
strFilter = "[Priority] =" & 'Today'
Click to expand...

Which one? The one you cleaned up or the mess I posted ;) You have no idea how hard that beast has been to move around to different forms as I add things to it.

Surprisingly (surprised me anyways) what I posted worked all the way through. I haven't tried updating it to yours yet. I have been trying to format a report that is too wide. I need all the data so I will likely end up cheating with the paper size. Le sigh
 
auntielo said:
Thanks for the clean up, if I was uncertain, I would always add extra to be safe. I did that a lot with ( ) in Boolean. If one set is good, 3 must be better.... well, at least 3 won't hurt.... maybe
Click to expand...

Not technically wrong, BUT if as likely in this case you need to change the strFilter, you have to do it in multiple places and increases the chance of errors, even with copy and paste.

However that all comes down to experience, but I always appreciate if someone gives me a better way of doing something.

With you there with only coming back to the code as and when you can. That is pretty much my situation in work. :D
 
I read the first post & noticed this part:

I am stuck on four: This Week, Next Week, Last Week, Before Last Week.
Click to expand...

Then I saw the other posts between you & Gasman & got a bit lost....
Do you still need help doing date filters for this week etc or are you OK now?
 
Not quite yet. I have This Week, Next Week, Last Week all showing up in my StartDate and EndDate txt, but I can't figure out how to do before last week. I could probably just use last year, but where is the fun in that?

Maybe I can just do that one from the filter? Speaking of which - it is entirely possible my brain is fried after a long day of access, but how do I rewrite this now that I am using the StartDate and EndDate?

Code: 
Case "DueDate"

strFilter = "[DueDate]=" & "#" & Me![DateFilter] & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "RequestDate"

strFilter = "[RequestDate]=" & "#" & Me![DateFilter] & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

The article I got the date formats from says to put this in the criteria, but I didn't know if that was the same.

Code: 
"Between [Forms]![FilterReport]![StartDate] And [Forms]![FilterReport]![EndDate]"


I am playing around with these two problems now, if you have any ideas, I would love to see them.
 
:D

Thanks to another google search, I GOT IT!!! Whew!

I am so close to done. Or at least done enough.

Here is what I ended up with (Gasman, I haven't changed it to the cleaner code yet, but it's on the list!)

Code: 
Private Sub cboReportField_AfterUpdate()

Me.Filterby.RowSource = "SELECT Filterby, TableField FROM FieldFilterOptions WHERE [TableField] = '" & Me.cboReportField & "' ORDER BY [Filterby]"

End Sub

Private Sub Filterby_AfterUpdate()

If Me.Filterby.Value = "Today" Then
Me.DateFilter = Date

ElseIf Me.Filterby.Value = "Tomorrow" Then
Me.DateFilter = Date + 1

ElseIf Me.Filterby.Value = "Yesterday" Then
Me.DateFilter = Date - 1

ElseIf Me.Filterby.Value = "This Week" Then
Me.StartDate = Date - Weekday(Date) + 1
Me.EndDate = Date - Weekday(Date) + 7

ElseIf Me.Filterby.Value = "Last Week" Then
Me.StartDate = Date - Weekday(Date) + 1 - 7
Me.EndDate = Date - Weekday(Date) + 7 - 7

ElseIf Me.Filterby.Value = "Next Week" Then
Me.StartDate = Date - Weekday(Date) + 1 + 7
Me.EndDate = Date - Weekday(Date) + 7 + 7

ElseIf Me.Filterby.Value = "Before Last Week" Then
Me.StartDate = #1/1/2015#
Me.EndDate = Date - Weekday(Date) + 1 - 7

End If

End Sub

Private Sub Command153_Click()

Dim strFilter As String

Select Case Me.cboReportField.Value

Case "Priority"

strFilter = "[Priority] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter


Case "TaskCategory"

strFilter = "[TaskCategory] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "TaskDescription"

strFilter = "[TaskDescription] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "ReportNeeded"

strFilter = "[ReportNeeded] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "DueDate"

strFilter = "[DueDate] BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "RequestDate"

strFilter = "[RequestDate] BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

End Select

End Sub
 
Hi

Glad you sorted it before I replied. My solution was more complicated than the above!
 
I know you got caught up in the code but look at this line?;)

Code: 
Me.EndDate = Date - Weekday(Date) + 7 - 7

Well done for getting it sorted though.:cool:

auntielo said:
:D

Thanks to another google search, I GOT IT!!! Whew!

I am so close to done. Or at least done enough.

Here is what I ended up with (Gasman, I haven't changed it to the cleaner code yet, but it's on the list!)

Code: 
Private Sub cboReportField_AfterUpdate()

Me.Filterby.RowSource = "SELECT Filterby, TableField FROM FieldFilterOptions WHERE [TableField] = '" & Me.cboReportField & "' ORDER BY [Filterby]"

End Sub

Private Sub Filterby_AfterUpdate()

If Me.Filterby.Value = "Today" Then
Me.DateFilter = Date

ElseIf Me.Filterby.Value = "Tomorrow" Then
Me.DateFilter = Date + 1

ElseIf Me.Filterby.Value = "Yesterday" Then
Me.DateFilter = Date - 1

ElseIf Me.Filterby.Value = "This Week" Then
Me.StartDate = Date - Weekday(Date) + 1
Me.EndDate = Date - Weekday(Date) + 7

ElseIf Me.Filterby.Value = "Last Week" Then
Me.StartDate = Date - Weekday(Date) + 1 - 7
Me.EndDate = Date - Weekday(Date) + 7 - 7

ElseIf Me.Filterby.Value = "Next Week" Then
Me.StartDate = Date - Weekday(Date) + 1 + 7
Me.EndDate = Date - Weekday(Date) + 7 + 7

ElseIf Me.Filterby.Value = "Before Last Week" Then
Me.StartDate = #1/1/2015#
Me.EndDate = Date - Weekday(Date) + 1 - 7

End If

End Sub

Private Sub Command153_Click()

Dim strFilter As String

Select Case Me.cboReportField.Value

Case "Priority"

strFilter = "[Priority] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter


Case "TaskCategory"

strFilter = "[TaskCategory] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "TaskDescription"

strFilter = "[TaskDescription] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "ReportNeeded"

strFilter = "[ReportNeeded] =" & "'" & Me.Filterby & "'"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "DueDate"

strFilter = "[DueDate] BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

Case "RequestDate"

strFilter = "[RequestDate] BETWEEN #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
DoCmd.OpenReport "ToDoReport", acViewReport, , strFilter

End Select

End Sub
Click to expand...
 
You must log in or register to reply here.

Similar threads

T
Filter Form with multiple Unbound combo box and dates
Replies
15
Views
1,129
MajP
MajP
C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
738
CraigDouglas
C
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
599
CJ_London
C
G
Help with unbound form to filter report
Replies
9
Views
730
gojets1721
G
A
Use VBA to change Report parameter. DoCmd.SetParameter
Replies
11
Views
1,153
anymuon
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom