Filtering by Dates

[Andy Roberts]

I have a form which lists all my projects from day dot (all with dates
stores as dd/mm/yyyy). When I open the form I want either cbo boxes or
radio butoons which when used will filter the form by a date range.

e.g. I would like to select the year from the first cbo, then the month from
the second combo then the day from the third cbo and the continuous form
will sort automatically as each cbo is selected.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

 

[Dorian]

And what is your question?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Andy Roberts]

Dorian

I would like to select the year from the first cbo, then the month from the
second combo then the day from the third cbo and the continuous form will
sort automatically as each cbo is selected. Can this be done and is this
the best way to filter by date / date ranges?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

 

[Rick Brandt]

Dorian

I would like to select the year from the first cbo, then the month from
the second combo then the day from the third cbo and the continuous form
will sort automatically as each cbo is selected. Can this be done and
is this the best way to filter by date / date ranges?

Let's take this in layers. A basic example of filtering on a date if you
were entering a full date in a TextBox would be...

Me.Filter = "DateField = #" & Me.DateTextBox & "#"
Me.FilterOn = True

Now, you need to do that but by constructing the date out of three
ComboBox selections...

Dim FilterDate as Date
FilterDate = DateSerial(Me.YearCombo, Me.MonthCombo, Me.DayCombo)

Me.Filter = "DateField = #" & _
Format(FilterDate,"yyyy-mm-dd") & "#"
Me.FilterOn = True

Further, you need logic that uses code similar to the above but where a
valid FilterDate is still produced even when a selection has not been
made for the month and day. By the way, are you making it a requirement
that a Year must always be chosen so we count on that always being
supplied? For now I am assuming that to be the case.

Not only do you need to deal with a month and day not being selected, but
now your date filter will have to be for a range rather than a single
value. Actually, if the Dates in your table contain a non-midnight time
component then you would have needed a range for the above examples as
well. To that end we need a FilterDateStart and a FilterDateEnd rather
than simply a single FilterDate.

Dim FilterDateStart as Date
Dim FilterDateEnd as Date

If IsNull(Me.MonthCombo) Then
FilterDateStart = DateSerial(Me.YearCombo, 1, 1)
FilterDateEnd = DateSerial(Me.YearCombo + 1, 1, 1)
Else
FilterDateStart = DateSerial(Me.YearCombo, _
Me.MonthCombo, _
Nz(Me.DayCombo, 1))

If IsNull(Me.DayCombo) Then
FilterDateEnd = DateSerial(Me.YearCombo, _
Me.MonthCombo + 1, 1)
Else
FilterDateEnd = DateSerial(Me.YearCombo, _
Me.MonthCombo, _
Me.DayCombo + 1)
End If
End If

Me.Filter = "DateField >= #" & _
Format(FilterDateStart,"yyyy-mm-dd") & "# " & _
"AND DateField < #" & _
Format(FilterDateEnd,"yyyy-mm-dd") & "#"
Me.FilterOn = True

I am making an assumption above that the user is not allowed to leave the
month blank and still select a date. If that were done then the code
above for end date would produce unexpected results. What I have there
allows for selecting a month without a date, but not a date without a
month.

Notice that I also go one day past the nominal end date but I use < for
that test rather that <=. This way the code works even if your dates in
the table have a time-component.

You would have to put this code in a sub-routine of your form and call it
in the AfterUpdate event of all three of your ComboBoxes. I would also
recommend that you disable the month and date Combos by default. The
month Combo would be enabled after they select a year and the date Combo
would be enabled when they select a month.

All of the above is untested air-code intended to give you the basic
idea. I could very well have a few problems in there, but it's a place
to start.

 

[Andy Roberts]

Rick

Thanks for your thorough answer which in the main makes sense to me. I've
implemented it as follows:-

3 combos called cboSelectYear, cboSelectMonth, cboSelectDay. Each looks up
values in 3 tables tblYear, tblMonth, tblDay.

I disable the month and day cbo by the form load event:-

Private Sub Form_Load()

cboSelectMonth.Enabled = False
cboSelectDay.Enabled = False

End Sub

Problem 1 :- I cant get the month cbo to enable when I select a year (and
subsequently the day cbo when I select a month). I am using the following
on the cboSelectYear after update event...

Private Sub cboSelectYear_AfterUpdate()
DateFilter
'this is the subroutine which sort the form

If cboSelectYear = Not Null Then
Me.cboSelectMonth.Enabled = True
Me.cboSelectDay.Enabled = True
Else
Me.cboSelectMonth.Enabled = False
Me.cboSelectDay.Enabled = False
End If

Me.cboSelectMonth = Null
Me.cboSelectDay = Null

End Sub

I have a cmd button at the end which clear all 3 combos using the on click
event...

Private Sub cmdClearReceivedDate_Click()
Me.cboSelectYear = Null
Me.cboSelectMonth = Null
Me.cboSelectDay = Null
End Sub

I have created a sub routine called DateFilter as follows which is called on
the after update event of all 3 cbos:-

Sub DateFilter()
Dim FilterDateStart As Date
Dim FilterDateEnd As Date

If IsNull(Me.cboSelectMonth) Then
FilterDateStart = DateSerial(Me.cboSelectYear, 1, 1)
FilterDateEnd = DateSerial(Me.cboSelectYear + 1, 1, 1)
Else
FilterDateStart = DateSerial(Me.cboSelectYear, _
Me.cboSelectMonth, _
Nz(Me.cboSelectDay, 1))

If IsNull(Me.cboSelectDay) Then
FilterDateEnd = DateSerial(Me.cboSelectYear, _
Me.cboSelectMonth + 1, 1)
Else
FilterDateEnd = DateSerial(Me.cboSelectYear, _
Me.cboSelectMonth, _
Me.cboSelectDay + 1)
End If
End If

Me.Filter = "ReceivedDate >= #" & _
Format(FilterDateStart, "yyyy-mm-dd") & "# " & _
"AND ReceivedDate < #" & _
Format(FilterDateEnd, "yyyy-mm-dd") & "#"
Me.FilterOn = True

End Sub


Problem 2:- When I select a year the form filter returns no results. The
field I am filtering is ReceivedDate and is formatted as a shortdate (there
is valid data in the field for the filter to return at least one matching
result)

Questions

1. Are the year, month and day fields in each of the tables which populate
the cbos ok to be stored as text?
2. If I clear the cbo values (using my button) should the form return all
the results?
3. I have a second date on the form called SentDate. I could repeat all of
the above and have a second set of controls when operate on the SentDate
field. My gut feeling is an option group would change whether a single set
of cbos operated on either date field, but this is well above my knowledge
base. How difficult is this to implement.

Thanks for all your help
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK