Compare Dates

[Scotty7]

I'm trying select data between two dates using the datetime picker control.
How do I force Todays date to include the time 11:59:59 PM. I've tried a
bunch of ways but have not succeeded.
Thanks.
Scott

 

[Jeff Boyce]

Scott

If you truly only need to compare dates, don't use the date/time values, use
Date() (not Now()).

If you already have date/time values recorded, you can still test them by
retrieving only the date portion with the DateValue() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

How are you trying to do this? As parameters for a query?

Instead of

BETWEEN Forms!MyForm!MyCalendar1 AND Forms!MyForm!MyCalendar2

use

BETWEEN Forms!MyForm!MyCalendar1 AND DateAdd("s", 86399,
Forms!MyForm!MyCalendar2)

or

BETWEEN Forms!MyForm!MyCalendar1 AND (Forms!MyForm!MyCalendar2 +
DateSerial(23, 59, 59))

 

[Scotty7]

Thanks for the reply Jeff, It's still not working for me. here's what I'm
doing...

gStartDate = DateValue(Date - 7) ' gStartDate is a date type
gEndDate = DateValue(Date) ' gEndDate is a date type
.....

str = "SELECT distinct d_date FROM table"
str = str & " WHERE d_date >= '" & gStartDate & "'"
str = str & " and d_date <= '" & gEndDate & "'"

'the d_date in SQL Server is datetime format
It will not return any rows for todays date, even though there are many
records for today.

Thanks again for any assistance. This is slightly frustrating

 

[John Spencer]

Small changes to include everything for the end date when the date field has a
time.

gStartDate = DateValue(Date() - 7) ' gStartDate is a date type
gEndDate = DateValue(Date() + 1) ' gEndDate is a date type
.....

str = "SELECT distinct d_date FROM table"
str = str & " WHERE d_date >= '" & gStartDate & "'"
str = str & " and d_date <='" & gEndDate & "'"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County