RE: Problem filtering records using date

Ed:

To use Filter by Form you'd need to enter the complete date time value; the
formatting of the controls to show the date and time of day separately has no
bearing on the issue. Rather than using Filter by Form you might like to
consider a more flexible approach by adding two unbound text boxes to your
form, txtStartDate and txtEndDate, along with a button to implement the
filter. That way you can select records for one day (by entering the same
date in each) or a range of days by entering different dates. To implement
the filter put the following code in the button's Click event procedure:

Dim strFilter As String

strFilter = "[YourDateTimeField] >= #" & _
Format(Me.txtStartDate,"mm/dd/yyyy") & _
"# And [YourDateTimeField] < #" & _
Format(Me.txtEndDate,"mm/dd/yyyy") & _
"# +1"

Me.Filter = strFilter
Me.FilterOn = True

The date values entered in the text boxes is actually a date/time value at
midnight at the start of the day, there being no such thing in Access as a
date value without a time of day, so looking for dates on or after the start
date and before the day after the end date will return all records in the
range whatever the time of day element in the field may be.

If you are going to be filtering for one day most of the time you can avoid
having to enter the date twice by putting the following in the AfterUpdate
event procedure of txtStartDate:

Me.txtEndDate = Me.txtStartDate

The end date will automatically be given the same value as the start date,
but you can overwrite it if you want to filter over a longer date range.

To clear the filter and show all records you can use the built in button on
the toolbar, or add another button to the form with the following in its
Click event procedure, which will also clear the unbound text boxes:

Me.FilterOn = False
Me.txtStartDate = Null
Me.txtEndDate = Null

Ken Sheridan
Stafford, England

"ehunter" wrote:

I have a telephone log database but am having problems filtering for all
records for a particular date using Filter By Form. The underlying table has
1 DateTime field using the General Date format and it's populated through a
data entry form. All forms are based on queries.

The form used for filtering is a read only form that's been set up with (2)
copies of the DateTime field. The first (txtDate) is formatted for Short Date
and the second (txtTime) is formatted for Medium Time to visually separated
the date and time for the user. When I enter a date in txtDate using Filter
By Form, I don't get any records back. I've tried a number of things like
using the asterisk wildcard after the date but get an error message (invalid
date). I've also tried a couple of calculated fields in the query, but still
can't get records back.

Any idea what I am doing wrong? Thanks.
--
Ed

.