Trying to filter a form to only values in a date field that are older than today

[Tim F]

Hi all,

I am using Access 2007 in Win XP.

I have a field named Date_for_resolu tion in a table. I am trying to make a button that opens a tabular (continuous) form I have created, and filter it to display only records from the table where Date_for_resolu tion is older than the current date.

I have the following code at the moment but it is giving me Run-time error 3464 - Data type mismatch in criteria expression.

Private Sub Button1_Click()
frm_filter = "Date_for_resolution < '" & Date & "'"
DoCmd.OpenForm "frmIssuesTabular", , , frm_filter
End Sub

I have also tried using

frm_filter = "DateValue(Date_for_resolution) < '" & Date & "'"

but experienced the same run-time error.

Can anyone spot what I am doing wrong here?

Many thanks for any suggestions!

[Jerry Maiapu]

The easiest method is to filter the form's ("frmIssuesTabula r")underlyin g query.
On the criteria row under Date_for_resolu tion column put

=<Date

NB: To access the underlying query of the form follow these step:

1.In the Navigation Pane, right-click the form, and then click Design View on the shortcut menu. The object opens in Design view.

2.If the property sheet is not already visible, press F4 to display it.

3. Click in the object's Record Source property box, and then click ... sign. The query designer appears, with the object's recordset query displayed.

[Tim F]

Thanks for the tips! If I create a query as you suggest, will this affect the form when opened without the new button I am creating? This form has a range of different ways to open with various filters applied, so I want to avoid changing the actual form directly if possible.

[Tim F]

Solved. Many thanks for the assist.

Private Sub Label38_Click()
frm_filter = "Date_for_resolution < date()"
DoCmd.OpenForm "frmIssuesTabular", , , frm_filter
End Sub

[Jerry Maiapu]

Just noted that your two posted events have different object names. Did you intend to do so?
Post#1

[B] Private Sub Button1_Click()[/B]

Your Solution Post#4

[B]Private Sub Label38_Click()[/B]