Re: Filtering a query for a form based on dates

Tech-Archive recommends: Speed Up your PC by fixing your registry

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()

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"xsdaver via AccessMonster.com" <u45689@uwe> wrote in message
news:8937347271612@xxxxxx
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

--
Message posted via http://www.accessmonster.com



.

Relevant Pages

  • Re: Handling floating point with decimal comma separator to filter a form
    ... Lets say the user wants to filter values between 1,1 and 1,9 (note that the user wiil enter the number with a comma sparator, not with a dot - and the Standard format will be of no help here). ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)
  • Re: How to save several variables with the same prefix?
    ... well the problem is that if I write fprintf(' filename', X) in each iteration it will create only one file named filename, it will overwrite it each time and I end up with only one file that has the value of X but just for s=11. ... is create the string this is why I put it inicialy without ' '. ... Thanks for answer me why fwrite did not work, that was somenthing I did not know but after fprintf did not work I begun to explore other ideas, I need it with format so fwrite is not an option. ... name that references the size of the window (filter) I am using in my ...
    (comp.soft-sys.matlab)
  • Re: Handling floating point with decimal comma separator to filter a form
    ... Standard format will be of no help here). ... a desire to filter this field so it lies between the 2 numbers ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)
  • Re: Handling floating point with decimal comma separator to filter a form
    ... a desire to filter this field so it lies between the 2 numbers ... If that doesn't work out, print the filter string to the Immediate Window, this filter string needs to in the standard US format, just like the WHERE clause of a query. ...
    (microsoft.public.access.formscoding)
  • Re: Date format detection
    ... Specifies the locale for which the date string is to be formatted. ... date format for this locale. ... the system default-date format for the specified locale. ... be enclosed within single quotation marks in the date format picture. ...
    (borland.public.delphi.thirdpartytools.general)