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

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

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

"Song Su" wrote:

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

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


Song Su wrote:
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]));

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

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.

"ruralguy via AccessMonster.com" u12102@uwe wrote in message
news:758558247fcc0@uwe...
Try:
WHERE DateValue(Log.LoginTime) Between [Forms]![MainMenu].[cboFrom] And
[Forms]![MainMenu].[cboTo];


Song Su wrote:
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]));

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.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?

Song Su wrote:
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]));

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

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
http://I.Am/DougSteele
(no e-mails, please!)


"ruralguy via AccessMonster.com" u12102@uwe wrote in message
news:759a9d6e9db58@uwe...
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?

Song Su wrote:
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]));

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

Douglas J. Steele wrote:
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]));

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

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
http://I.Am/DougSteele
(no e-mails, please!)


"ruralguy via AccessMonster.com" u12102@uwe wrote in message
news:759d18cd69523@uwe...
Doug,
Wouldn't the DateValue() function strip out the time portion so you could
compare date to date?

Douglas J. Steele wrote:
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]));

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.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 ??

Douglas J. Steele wrote:
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]));

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1

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
http://I.Am/DougSteele
(no e-mails, please!)


"ruralguy via AccessMonster.com" u12102@uwe wrote in message
news:759d825bc8744@uwe...
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 ??

Douglas J. Steele wrote:
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]));

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200707/1