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

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

"Scotty7" <(E-Mail Removed)> wrote in message
news:73CE3E3E-CDAE-4FF9-890A-(E-Mail Removed)...
> 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
>
>

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Scotty7" <(E-Mail Removed)> wrote in message
news:73CE3E3E-CDAE-4FF9-890A-(E-Mail Removed)...
> 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
>
>

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

"Jeff Boyce" wrote:

> 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
>
> "Scotty7" <(E-Mail Removed)> wrote in message
> news:73CE3E3E-CDAE-4FF9-890A-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

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

Scotty7 wrote:
> 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
>
> "Jeff Boyce" wrote:
>
>> 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
>>
>> "Scotty7" <(E-Mail Removed)> wrote in message
>> news:73CE3E3E-CDAE-4FF9-890A-(E-Mail Removed)...
>>> 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
>>>
>>>
>>
>>