<= a date to include all the records (time problem)

[Song Su]

I have two combo boxes to allow user to select date range. My LogInTime
field has date and time. My following query would not select [cboTo] date,
only the day earlier. (If user, for example, select 7/20/07 in the cboTo, it
only displays records upto 7/19/07). If I add [cboTo]+1 and it says it's too
complecated.

SELECT Log.LoginTime
FROM Log
WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[Mike]

Not sure but try this
WHERE (((Log.LoginTime) Between [Forms]![MainMenu]![cboFrom] And
[Forms]![MainMenu]![cboTo]))

 

[ruralguy via AccessMonster.com]

Try:
WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo];

I have two combo boxes to allow user to select date range. My LogInTime
field has date and time. My following query would not select [cboTo] date,
only the day earlier. (If user, for example, select 7/20/07 in the cboTo, it
only displays records upto 7/19/07). If I add [cboTo]+1 and it says it's too
complecated.

SELECT Log.LoginTime
FROM Log
WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[Song Su]

If my cboFrom and cboTo are not same day, it works fine. If they are same
day, no result shows in the query even though records exist.

Try:
WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo];

I have two combo boxes to allow user to select date range. My LogInTime
field has date and time. My following query would not select [cboTo] date,
only the day earlier. (If user, for example, select 7/20/07 in the cboTo,
it
only displays records upto 7/19/07). If I add [cboTo]+1 and it says it's
too
complecated.

SELECT Log.LoginTime
FROM Log
WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[ruralguy via AccessMonster.com]

Between is supposed to be inclusive. If you change the cboFrom by one day
does it then also pick up the records from that single day you said had
records?

If my cboFrom and cboTo are not same day, it works fine. If they are same
day, no result shows in the query even though records exist.

Try:
WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And

[quoted text clipped - 12 lines]

WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[Douglas J. Steele]

Between is inclusive, but since LogInTime contains both date and time, and
cboFrom and cboTo only include date, it's necessary to increment cboTo.

You need

WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
DateAdd("d", 1, [Forms]![MainMenu].[cboTo]);

although

WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo] + 1;

should have worked.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Between is supposed to be inclusive. If you change the cboFrom by one day
does it then also pick up the records from that single day you said had
records?

If my cboFrom and cboTo are not same day, it works fine. If they are same
day, no result shows in the query even though records exist.

Try:
WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And

[quoted text clipped - 12 lines]

WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[ruralguy via AccessMonster.com]

Doug,
Wouldn't the DateValue() function strip out the time portion so you could
compare date to date?

Between is inclusive, but since LogInTime contains both date and time, and
cboFrom and cboTo only include date, it's necessary to increment cboTo.

You need

WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
DateAdd("d", 1, [Forms]![MainMenu].[cboTo]);

although

WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo] + 1;

should have worked.

Between is supposed to be inclusive. If you change the cboFrom by one day
does it then also pick up the records from that single day you said had

[quoted text clipped - 8 lines]

WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[Douglas J. Steele]

Sorry, you're absolutely right. I missed the DateValue there.

However, applying DateValue to each row can be "expensive". It's far better
not to use the DateValue function, and add 1 to the End Date, since that
will only be executed once:

WHERE Log.LoginTime Between [Forms]![MainMenu].[cboFrom] And DateAdd("d", 1,
[Forms]![MainMenu].[cboTo])

or

WHERE Log.LoginTime Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo] + 1



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
Wouldn't the DateValue() function strip out the time portion so you could
compare date to date?

Between is inclusive, but since LogInTime contains both date and time, and
cboFrom and cboTo only include date, it's necessary to increment cboTo.

You need

WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
DateAdd("d", 1, [Forms]![MainMenu].[cboTo]);

although

WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo] + 1;

should have worked.

Between is supposed to be inclusive. If you change the cboFrom by one
day
does it then also pick up the records from that single day you said had

[quoted text clipped - 8 lines]

WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[ruralguy via AccessMonster.com]

Thanks Doug. Very good point but wouldn't you then pick up additional data
from the next day since Between is inclusive? Maybe switch to >= and <??

Sorry, you're absolutely right. I missed the DateValue there.

However, applying DateValue to each row can be "expensive". It's far better
not to use the DateValue function, and add 1 to the End Date, since that
will only be executed once:

WHERE Log.LoginTime Between [Forms]![MainMenu].[cboFrom] And DateAdd("d", 1,
[Forms]![MainMenu].[cboTo])

or

WHERE Log.LoginTime Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo] + 1

Doug,
Wouldn't the DateValue() function strip out the time portion so you could

[quoted text clipped - 21 lines]

WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[Douglas J. Steele]

Remember that the date data type is an 8 bytes floating point number, where
the integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day. In other words, 2:00 AM is stored as .083333, 4:00 AM is stored as
..166667, 6:00 AM is stored as .25 and so on. A date without a time is
midnight of that date (i.e.: the exact moment when the calendar changes from
the previous day to the given date). The odds of having an entry for
LoginTime with that precise time is extremely low, so it's usually
considered reasonable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug. Very good point but wouldn't you then pick up additional
data
from the next day since Between is inclusive? Maybe switch to >= and <??

Sorry, you're absolutely right. I missed the DateValue there.

However, applying DateValue to each row can be "expensive". It's far
better
not to use the DateValue function, and add 1 to the End Date, since that
will only be executed once:

WHERE Log.LoginTime Between [Forms]![MainMenu].[cboFrom] And DateAdd("d",
1,
[Forms]![MainMenu].[cboTo])

or

WHERE Log.LoginTime Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo] + 1

Doug,
Wouldn't the DateValue() function strip out the time portion so you
could

[quoted text clipped - 21 lines]

WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));

 

[ruralguy via AccessMonster.com]

D'oh! I knew that...I knew that! Thanks for the clarification Doug. 8^)

Remember that the date data type is an 8 bytes floating point number, where
the integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day. In other words, 2:00 AM is stored as .083333, 4:00 AM is stored as
.166667, 6:00 AM is stored as .25 and so on. A date without a time is
midnight of that date (i.e.: the exact moment when the calendar changes from
the previous day to the given date). The odds of having an entry for
LoginTime with that precise time is extremely low, so it's usually
considered reasonable.

Thanks Doug. Very good point but wouldn't you then pick up additional
data

[quoted text clipped - 22 lines]

WHERE (((Log.LoginTime)>=[Forms]![MainMenu].[cboFrom] And
(Log.LoginTime)<=[Forms]![MainMenu].[cboTo]));