Solved Filtering a form based on more than one selection (1 Viewer)

[Denise2020]

Hello all. I have a form with a list of projects. Each project has a project originating agency, and there is also a basic time tracker where the responsible project manager puts in an estmate for how much time was spent on the project.

I created a combobox in order to select a project originating agency, which selects all projects from that particular agency, and how much time was spent on all those projects (ie adding all the time spent on each project).

What I cannot do, and need to, is also select a date range/month/year, some way of selecting projects only during a specific time. My supervisor would, for example, like to see how much time was spent on one agency's projects, for a specific time range, be it for the year, month, or between certain dates. Right now it selects all projects from inception, which is not particularly helpful. How could I go about doing this?

I am sure I am leaving something out that you need to know but thank you in advance for the amazing help you always give!

 

[Ranman256]

the dates will always have a start & stop date, so put txtStartDate, & txtEndDate.
if you have other fields this filter checks them , then applies them:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(txtStartDate) then sWhere = sWhere & " and [DateFld] between #" & txtStartDate & "#  and #" & txtEndDate & "#"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboDept) then sWhere = sWhere & " and [Dept]='" & cboDept & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

 

[Denise2020]

That was just perfect! Thank you so much - exactly what I needed!

 

[Denise2020]

A new related question: I have a report based on this filter but for some reason it is not taking the dates into account. The report currently is based on a query with a WHERE clause to match the cboBox for the agency. How would I go about adding the date range WHERE clause? Thank you again!