Filter on date problem

[Stapes]

Hi
I am trying to display records whose ring-back-date is today.

Here is my code:

Dim strDate As String
strDate = Format(Date, "dd/mm/yyyy")
Me.Filter = "[Ring-back-date] = " & strDate
Me.FilterOn = True

The field [Ring-back-date] is defined as Short Date, input mask
00/00/0000;;_

Stapes

 

[Tom van Stiphout]

On Thu, 3 Jan 2008 05:44:47 -0800 (PST), Stapes

You're not stating what problem (if any) you are receiving, any error
messages?
I'm assuming the filter does not work because you don't have #-signs
around the date:
Me.Filter = "[Ring-back-date] = #" & strDate & "#"

Also make sure you understand the difference between a variable or
field of type Date, and a formatted date value. Formatting has nothing
to do with how Date values are stored, but is stictly a presentation
issue.

-Tom.

 

[Allen Browne]

You cannot use your own local date format in the Filter of the string: it
needs to be in the format expected by JET. It also needs # around it as the
delimiter:
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strWhere = "[Ring-back-date] = " & Format(Date, "\#mm/dd/yyyy\#")
Me.Filter = strWhere
Me.FilterOn = True

If your date field may have a time component in it, use:
strWhere = "([Ring-back-date] >= " & Format(Date, "\#mm/dd/yyyy\#") & ")
AND ([Ring-back-date] < " & Format(Date + 1, "\#mm/dd/yyyy\#")

 

[Tom van Stiphout]

On Thu, 3 Jan 2008 23:24:28 +0900, "Allen Browne"

Good point that I missed: regardless of your locale, Jet works with
the US date format for queries.
-Tom.

 

[Ron2006]

On Thu, 3 Jan 2008 23:24:28 +0900, "Allen Browne"


Good point that I missed: regardless of your locale, Jet works with
the US date format for queries.
-Tom.

You cannot use your own local date format in the Filter of the string: it
needs to be in the format expected by JET. It also needs # around it as the
delimiter:
   Dim strWhere As String
   If Me.Dirty Then Me.Dirty = False    'Save first.
   strWhere = "[Ring-back-date] = " & Format(Date, "\#mm/dd/yyyy\#")
   Me.Filter = strWhere
   Me.FilterOn = True

If your date field may have a time component in it, use:
   strWhere = "([Ring-back-date] >= " & Format(Date, "\#mm/dd/yyyy\#") & ")
AND ([Ring-back-date] < " & Format(Date + 1, "\#mm/dd/yyyy\#")- Hide quoted text -

- Show quoted text -

If [Ring-back-date] is a short date formated date field then I believe
the following should probably work also:

strWhere = "[Ring-back-date] = #" & date() & "#"

 

[Allen Browne]

Ron, I think you missed the points in the reply:

a) If the fields is formatted as Short Date, that's a really good way to
confuse the user about the presence of the time component. Setting the
Format property does not prevent a time being entered (typically because the
Default value was set to Now() instead of Date()); it just prevents Access
from displaying it. But the query will not return any records when you ask
for the particular date (except those entered at exactly midnight.)

b) The return value of Date() is the system date in your local format. In my
country, it returns 4/1/2008 today. If you append that into the string (as
in your example), the query will return records for April Fools Day, not 4th
January. For more inforamation about that, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

On Thu, 3 Jan 2008 23:24:28 +0900, "Allen Browne"


Good point that I missed: regardless of your locale, Jet works with
the US date format for queries.
-Tom.

You cannot use your own local date format in the Filter of the string: it
needs to be in the format expected by JET. It also needs # around it as
the
delimiter:
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save first.
strWhere = "[Ring-back-date] = " & Format(Date, "\#mm/dd/yyyy\#")
Me.Filter = strWhere
Me.FilterOn = True

If your date field may have a time component in it, use:
strWhere = "([Ring-back-date] >= " & Format(Date, "\#mm/dd/yyyy\#") & ")
AND ([Ring-back-date] < " & Format(Date + 1, "\#mm/dd/yyyy\#")- Hide
quoted text -

- Show quoted text -

If [Ring-back-date] is a short date formated date field then I believe
the following should probably work also:

strWhere = "[Ring-back-date] = #" & date() & "#"

 

[Ron2006]

I was not addressing a time component. in which case all bets are off
and you are absolutely right.

If a field is a date ie formated as a date and displayed as a date and
being treated as a date isn't it really a number and therefore the
comparison would work, no matter what the external format? Especially
with the # signs telling it that the enclosed item is a date.

I have not had to work with other than US formated dates and so have
not been bit by the problem. Just trying to glean knowledge from those
who have encountered the problem.

Ron

 

[Allen Browne]

A Date/Time value is internally stored as a special kind of floating point
number - that's correct.

The # delimiter informs JET that the value is to be read as a date. However,
JET interprets the format of a literal date in a string as being American
(mdy) if it can. That way, a query statement generates the same results
regardless of where it is executed. The trouble is that the output of Date()
is presented to the user in their local date format. Therefore if you merely
concatenate the string output of Date() into a string, and then use the
string in a query statement, you will get the wrong results if you live in a
country that does not use the US date format.

I realize this is a non-issue for many Americans who just make databases for
their own use. But it is a serious issue for developers whose software could
be used in the UK/Australia (dmy), Korea (ymd), etc.

Same issues apply to literal date values in VBA code.

 

[Ron2006]

Thanks for the explanation.

I shall add it to my notes table, and hope I remember it if I
encounter a problem.

Ron