Date filtering

  • Thread starter Thread starter Laurie
  • Start date Start date
L

Laurie

Using European date format (dd/mm/yy - or any standard
variant in European order) the following will not work

SelectFilter = "Students.[Date enrolled] > #" & StartDate
& "# And Students.[Date enrolled] < #" & EndDate & "# "

I can force it to work ONLY by manipulating StartDate and
EndDate as strings, swapping the day and month data then
setting SelectFilter - tedious and unsatisfactory...

It is pathetic that Access is so difficult to use with
dates. What's the problem - and more to the point is
there a solution?
 
Hi Laurie.

It is possible to work with Access dates in a country that uses dd/mm/yyyy.
We have done that for 11 years now, and have posted our experience in this
article:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

The article explains how Access interprets entries in the interface (forms,
query design view, etc) according to your regional settings, but requires
that you follow the expected date format in SQL clauses and VBA code. That's
the only way it would be possible to write a database that works
consistently in any country.
 
It is pathetic that Access is so difficult to use with
dates. What's the problem - and more to the point is
there a solution?
Click to expand...

It isn't Access that is being really difficult, but several nationalities
that refuse to play ball with an internationally-accepted date format.

Think of it this way:

(1) The Jet engine is completely internationally-insensitive. All date
strings passed to the engine must be in either #mm/dd/yyyy# or #yyyy-mm-dd#
formats (i.e. USian or ISO). This makes it very easy to transfer code from
one area to another without changes.

(2) VBA and the Access GUI is internationally-sensitive. Try this

? Day("03/05/07")

in USA and you'll get 5, and in UK you'll get 3. The same happens when you
enter dates into a text box. This generally makes things easy to transfer a
GUI application across areas.

The trouble comes when you cross (2) and (1). This can be obvious, passing
strings direct from a text box to a SQL statement; or implicit when casting
a function like Date(). As long as you keep each part separate, you'll
always be fine. Format all dates explicitly before they go near a SQL
statement. Validate all dates that the user types in before using them for
anything. Be defensive!

In the end, the VBA/ GUI/ Jet mixture makes a pretty good job of clarifying
a pretty murky area. As long as you keep a clear head, and always watch
your conversions explicitly, you won't go wrong.

HTH


Tim F
 
Back
Top