Filtering a query for a form based on dates

[xsdaver via AccessMonster.com]

I have a query that is used to provide data for a form. The query has a
field that identifies the date an item was sold. That field is specified as
a date field. In the form, I put a value into a text box and requery. The
only thing that will work if there are dates in the text box. If I put in
anything that you might otherwise put into the criteria filed of a query it
does not work. Things like Like "*", or >#1/1/2008# return an error message
that states the formula is too complex to use. I've tried with and without
quotes and the # symbol but nothing seems to work. Can what I want to do be
done?

Thanks,
Dave

 

[xsdaver via AccessMonster.com]

I should have also mentioned the only way anything works is if the format for
the text box on the form is also set to Date.

 

[Graham Mandeno]

Hi Dave

You haven't really given enough information about what you have tried for me
to hazard a guess at what might be causing the problem.

However, I can give you a couple of tips that might help.

SQL (the language used for Access queries and filters) is quite fussy about
date formats. The best format to use is either the North American format
(mm/dd/yyyy) or the international format (yyyy-mm-dd). I prefer the latter
because it's less ambiguous. Also, dates and times in SQL statements should
always be enclosed in hash/pound signs (#).

I find the following function useful for formatting dates for SQL:

Public Function SQLDate(ByVal d As Variant) As String
Dim sFormat As String
On Error Resume Next
d = CDate(d)
If Err = 0 Then
If TimeValue(d) = 0 Then
sFormat = "\#yyyy\-mm\-dd\#"
Else
sFormat = "\#yyyy\-mm\-dd hh\:nn\:ss\#"
End If
SQLDate = Format(d, sFormat)
Else
Err.Clear
End If
End Function

Now, when you wish to apply a filter to your form, I recommend that you do
it via the form's Filter property. As I understand it, you currently have
your recordsource query referring to textboxes on the form and you requery
the whole form when the textboxes are updated. This is unnecessarily
complicated.

Let's say you have two unbound textboxes, txtStartDate and txtEndDate.

Write a function in your form module like this:

Private Function SetFilter()
Const cAND as String = " AND "
Dim fltr as string
If Not IsNull(txtStartDate) then
sfltr = "([DateField]>=" & SQLDate(txtStartDate) & ")" & cAND
End If
If Not IsNull(txtEndDate) then
sfltr = "([DateField]<=" & SQLDate(txtEndDate) & ")" & cAND
End If
' add in as many other filter fragments as you require
If Len(fltr) = 0 then
Me.FilterOn = False
Else
' remove the last AND
Me.Filter = Left(fltr, Len(fltr) - Len(cAND))
Me.FilterOn = True
End If
End Function

Then, for txtStartDate and txtEndDate (and any other filtering controls) set
the AfterUpdate property to:
=SetFilter()

 

[xsdaver via AccessMonster.com]

Wow, thanks for the detailed response. I'm quite green at this and it took
me a bit to figure out what you were doing. I got it all working, but it
does not quite do what I'm looking for. I'll try to provide a better
description of what I'm trying to do.

I have a form with a subform. The main form's purpose in life is to find the
records to be displayed on the subform. Both forms are based on the same
query. The recordset can be the entire query result with no filters or,
hopefully, filtered results based on dates and other parameters. There are
no linked fields between the main form and subform. I have a combo box on
the main form that shows several columns of the query results. When you
select a row in the combo box it does a findfirst on the recordset for the
subform. The findfirst is looking for a unique value for each record, so it
finds the exact record you selected.

Ideally, the filters set up by the dates for [Date Sold] and other parameters
would filter both the combo box and the subform. I don't see a way to filter
the data for the combo box other than through the query. Even though the
filter set up by your code filters the records for the main form, the combo
box still shows all records. Can the string generated by your code be used
in a where portion of the Row Source query property?

I assume it is possible to apply the filter from the main form to the
subform if filters to the form end up being the way to go.

Dave

 

[Graham Mandeno]

Hi Dave

I don't quite understand why your main form needs to be bound to the query.

In fact, I don't see the need for a main form/subform at all.

If I understand you correctly, you have one or more textboxes to filter the
subform by [Date Sold] and perhaps other fields, and that you have a
combobox to select a specific record. You want the available rows in the
combobox to reflect only the filtered rows in the form.

I suggest you ditch the main form and use your subform as your main form.
Add the selection combobox and the filtering textboxes to the header or
footer of the form. Create a save query "qrySelectRecord" (or whatever) as
the RowSource of your combobox. Make sure that any fields that are used for
filtering are included in the query, even if they are out to the right hand
side of the columns that are used for display in the combo box.

At the end of the SetFilter function, add this code:

If Me.FilterOn Then
cboSelectRecord.RowSource = _
"Select * from qrySelectRecord where " & Me.Filter
Else
cboSelectRecord.RowSource = "qrySelectRecord"
End If

This will apply the same filter to your combo box as has been applied to
your form.

I hope I've understood you correctly. Post back if you still have a
problem.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

User the SetFilter code I gave you to set the filter on the form

Wow, thanks for the detailed response. I'm quite green at this and it
took
me a bit to figure out what you were doing. I got it all working, but it
does not quite do what I'm looking for. I'll try to provide a better
description of what I'm trying to do.

I have a form with a subform. The main form's purpose in life is to find
the
records to be displayed on the subform. Both forms are based on the same
query. The recordset can be the entire query result with no filters or,
hopefully, filtered results based on dates and other parameters. There
are
no linked fields between the main form and subform. I have a combo box on
the main form that shows several columns of the query results. When you
select a row in the combo box it does a findfirst on the recordset for the
subform. The findfirst is looking for a unique value for each record, so
it
finds the exact record you selected.

Ideally, the filters set up by the dates for [Date Sold] and other
parameters
would filter both the combo box and the subform. I don't see a way to
filter
the data for the combo box other than through the query. Even though the
filter set up by your code filters the records for the main form, the
combo
box still shows all records. Can the string generated by your code be
used
in a where portion of the Row Source query property?

I assume it is possible to apply the filter from the main form to the
subform if filters to the form end up being the way to go.

Dave

 

[xsdaver via AccessMonster.com]

I suspect much of the way I've done things can be blamed on ignorance.
However, thanks to your help it is now working the way I wanted. I have a
subform because the form changes based on what I'm doing. Same data but
displayed differently with more or less fields to reduce the amount of data
on the screen when it's not needed.