dates query

[Guest]

Hello, all,

I have a table that includes two fields that contain dates and I need a
query that will extract the dates that match 'today', or more to the point,
within a certain amount of days before or after 'today'. I've tried using
NOW in my query but it's not working.

Thanx,

Rip

 

[John Vinson]

Hello, all,

I have a table that includes two fields that contain dates and I need a
query that will extract the dates that match 'today', or more to the point,
within a certain amount of days before or after 'today'. I've tried using
NOW in my query but it's not working.

Thanx,

Rip

Now() is the current date and time, accurate to microseconds. It's
rather unlikely that a search for Date/Time values equal to Now() will
EVER return anything! A Date/Time value (no matter how it's formatted)
is stored as a Double Float number, a count of days and fractions of a
day since midnight, December 30, 1899. For example, from the Immediate
window:

?cdbl(now());Now()
38311.9486574074 11/20/2004 10:46:04 PM


Instead, use a range of dates. You need to know if the table field
contains pure dates, or dates and times; if the field is filled using
the Date() function or by typing in mm/dd/yy dates, it will have a
time portion of 0, and will correspond to midnight on the stated day.
To get a range of days from five days ago to today - any time today,
between midnight and 11:59:59.9999999 pm - use

DateAdd("d", -5, Date()) AND < DateAdd("d", 1, Date())

The Date() function returns today's date; DateAdd adds the specified
number of days (the "d" operator can be changed to anything from "s"
for seconds to "yyyy" for years, see the online help in the VBA
editor).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Outstanding, thanx for the help.

Rip

Now() is the current date and time, accurate to microseconds. It's
rather unlikely that a search for Date/Time values equal to Now() will
EVER return anything! A Date/Time value (no matter how it's formatted)
is stored as a Double Float number, a count of days and fractions of a
day since midnight, December 30, 1899. For example, from the Immediate
window:

?cdbl(now());Now()
38311.9486574074 11/20/2004 10:46:04 PM


Instead, use a range of dates. You need to know if the table field
contains pure dates, or dates and times; if the field is filled using
the Date() function or by typing in mm/dd/yy dates, it will have a
time portion of 0, and will correspond to midnight on the stated day.
To get a range of days from five days ago to today - any time today,
between midnight and 11:59:59.9999999 pm - use


The Date() function returns today's date; DateAdd adds the specified
number of days (the "d" operator can be changed to anything from "s"
for seconds to "yyyy" for years, see the online help in the VBA
editor).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps