Problem filtering records using date

[Guest]

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.

 

[Guest]

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

 

[Guest]

Ken, thanks much for your advice. I'll use your recommendation but I may have
an additional problem.

There are other fields that can be used in the filtering process, in
addition to the date field. We use Company Name, Rig Name, Company Rep (all
of which are lookup combo boxes in the main database table), and a memo
field, to further refine the filter, which was a reason for using Filter By
Form. I would like to use the approach you recommend, and also include values
from the above fields in the filter, if needed.

How should I set things up to gather any additional information for the
filter? Do I need to make a separate form to gather all of the filter values
and then use VBA to apply the filter? Or is there a simpler way? I'm trying
to make this filter process as simple as possible for my users, who are not
very proficient with Access. Any help would be greatly appreciated.

 

[Guest]

Ed:

Rather than cluttering up the bound form with unbound controls you might
prefer to use a separate unbound dialogue form, frmFilterDlg say, which can
be opened from a button on the main bound form. The code for the button
would pass the name of the bound form to the dialogue form like so:

DoCmd.OpenForm "frmFilterDlg", _
WindowMode:=acDialog, _
OpenArgs:=Me.Name

On the dialogue form you'd have text boxes txtStartDate and txtEndDate for
the dates again, but also controls for the other fields on which you might
want to filter the bound form. Some can be combo boxes, cboCompany, cboRig,
cboCompanyRep, while that for the memo field would be a text box, txtNotes
say.

The combo boxes would reproduce those on the bound form exactly, but without
having any value for their ControlSource properties, so you can just copy and
paste them from the main form and then delete their ControlSources. This
would mean you'd be able to select from them rather than typing in the names.
The memo field could be set up so that you can enter any substring and
return records where that substring is included in the memo field.

I'm assuming that the company, rig and company reps are recorded in the
telephone log table by numeric foreign keys which reference the primary keys
of the respective referenced tables (even though you see the names in the
combo boxes their values are the hidden numbers); I'll call these fields
CompanyID, RigID and CompanyRepID for this example. I'll call the memo field
Notes.

In the dialogue form you'd probably want two buttons , one to implement the
filter and one to cancel the operation and return to the bound form without
changing its filter. For the Cancel button simply close the form with:

DoCmd.Close acForm, Me.Name

For the button to implement the filter you'd need to examine each of the
unbound controls to see if they are Null or not and build a string expression
for the filter, then apply this to the main bound form and close the dialogue
form, so the code would go like this:

Dim frm As Form
Dim strFilter As String

Set frm = Forms(Me.OpenArgs)

' examine each control and build string for filter
If Not IsNull(Me.txtStartDate) Then
strFilter = " And " & _
YourDateTimeField] >= #" & _
Format(Me.txtStartDate,"mm/dd/yyyy") & _
"# And [YourDateTimeField] < #" & _
Format(Me.txtEndDate,"mm/dd/yyyy") & _
"# +1"
End If

If Not IsNull(Me.cboComany) Then
strFilter = strFilter & " And " & _
"CompanyID = " & Me.cboCompany
End If

If Not IsNull(Me.cboRig) Then
strFilter = strFilter & " And " & _
"RigID = " & Me.cboRig
End If

If Not IsNull(Me.cboComanyRep) Then
strFilter = strFilter & " And " & _
"CompanyRepID = " & Me.cboCompanyRep
End If

If Not IsNull(Me.txtNotes) Then
strFilter = strFilter & " And " & _
"Notes Like ""*" & Me.txtNotes & "*"""
End If

' remove redundant leading " And "
strFilter = Mid$(strFilter,6)

' set filter property of bound form
frm.Filter = strFilter
frm.FilterOn = True

' close dialogue form
DoCmd.Close acForm, Me.Name

You'll see from the above that it would be a relatively simple task to add
other controls to the dialogue form for other fields if you wished; you'd
just need to include another If….End If construct in the code for each extra
control.

If the foreign keys are 'natural' keys, i.e. the actual text names of the
company, rig and rep rather than numeric IDs you'd need to wrap the values in
quotes, e.g.

"CompanyID = """ & Me.cboCompany & """"

Don't forget that if you include spaces or other special characters in any
control or field names you need to enclose the name in brackets [like so] in
the code.

Ken Sheridan
Stafford, England

 

[Guest]

Ken:

Biggest thanks for your guidance on this issue. I implemented your
suggestions and the new form works beautifully. My users will certainly have
no problems using it to filter for what they need. And I was able to learn a
few new things about coding that should help me in the future. I did have to
use the code for "natural" foreign keys which is what I have in the lookup
tables. If you don't mind, please answer one more question: When designing
single field lookup tables, should I use an autonumber field as the primary
key, or is a "natural" key OK? Is one recommended over the other?

As a novice developer, I am really appeciative of folks like you and others
in these discussion groups who have such a grasp of Access & VBA, and are
able & willing to help people like me. Thanks again for walking me through to
a solution.
--
Ed

Ed:

Rather than cluttering up the bound form with unbound controls you might
prefer to use a separate unbound dialogue form, frmFilterDlg say, which can
be opened from a button on the main bound form. The code for the button
would pass the name of the bound form to the dialogue form like so:

DoCmd.OpenForm "frmFilterDlg", _
WindowMode:=acDialog, _
OpenArgs:=Me.Name

On the dialogue form you'd have text boxes txtStartDate and txtEndDate for
the dates again, but also controls for the other fields on which you might
want to filter the bound form. Some can be combo boxes, cboCompany, cboRig,
cboCompanyRep, while that for the memo field would be a text box, txtNotes
say.

The combo boxes would reproduce those on the bound form exactly, but without
having any value for their ControlSource properties, so you can just copy and
paste them from the main form and then delete their ControlSources. This
would mean you'd be able to select from them rather than typing in the names.
The memo field could be set up so that you can enter any substring and
return records where that substring is included in the memo field.

I'm assuming that the company, rig and company reps are recorded in the
telephone log table by numeric foreign keys which reference the primary keys
of the respective referenced tables (even though you see the names in the
combo boxes their values are the hidden numbers); I'll call these fields
CompanyID, RigID and CompanyRepID for this example. I'll call the memo field
Notes.

In the dialogue form you'd probably want two buttons , one to implement the
filter and one to cancel the operation and return to the bound form without
changing its filter. For the Cancel button simply close the form with:

DoCmd.Close acForm, Me.Name

For the button to implement the filter you'd need to examine each of the
unbound controls to see if they are Null or not and build a string expression
for the filter, then apply this to the main bound form and close the dialogue
form, so the code would go like this:

Dim frm As Form
Dim strFilter As String

Set frm = Forms(Me.OpenArgs)

' examine each control and build string for filter
If Not IsNull(Me.txtStartDate) Then
strFilter = " And " & _
YourDateTimeField] >= #" & _
Format(Me.txtStartDate,"mm/dd/yyyy") & _
"# And [YourDateTimeField] < #" & _
Format(Me.txtEndDate,"mm/dd/yyyy") & _
"# +1"
End If

If Not IsNull(Me.cboComany) Then
strFilter = strFilter & " And " & _
"CompanyID = " & Me.cboCompany
End If

If Not IsNull(Me.cboRig) Then
strFilter = strFilter & " And " & _
"RigID = " & Me.cboRig
End If

If Not IsNull(Me.cboComanyRep) Then
strFilter = strFilter & " And " & _
"CompanyRepID = " & Me.cboCompanyRep
End If

If Not IsNull(Me.txtNotes) Then
strFilter = strFilter & " And " & _
"Notes Like ""*" & Me.txtNotes & "*"""
End If

' remove redundant leading " And "
strFilter = Mid$(strFilter,6)

' set filter property of bound form
frm.Filter = strFilter
frm.FilterOn = True

' close dialogue form
DoCmd.Close acForm, Me.Name

You'll see from the above that it would be a relatively simple task to add
other controls to the dialogue form for other fields if you wished; you'd
just need to include another If….End If construct in the code for each extra
control.

If the foreign keys are 'natural' keys, i.e. the actual text names of the
company, rig and rep rather than numeric IDs you'd need to wrap the values in
quotes, e.g.

"CompanyID = """ & Me.cboCompany & """"

Don't forget that if you include spaces or other special characters in any
control or field names you need to enclose the name in brackets [like so] in
the code.

Ken Sheridan
Stafford, England