Query for all recs Between 7/10 at 2:PM and 7/12 at 6 PM ???

[kev100 via AccessMonster.com]

Been struggling with various queries for time/date criteria and have gotten
some Fantastic help in this forum...

The current query will display all records created after a Certain Day and a
certain time (ON THAT DAY).

.....in other words.......if at Start Day of 7/10/06 is entered and a Start
Time of 2 PM.....it will show all records on 7/10 AFTER 2 PM....But all
records created at ANY Day at ANY Time LATER than 7/10 (7/11, 7/12, etc).


However....I'm needing to show all records created BETWEEN 7/10 at 2PM (2PM
and greater...but only on 7/10) and 7/12 at 6 PM (but not after 6 PM...ON
7/12 or later).....Records created at ANY Time on 7/11 would need to be
included.

I'm just having a hard time figuring out how to Tie the Start/End TIME
parameters UNIQUELY to the Start/End DAYS.......in a Parameter query.

Any help appreciated.

THANKS

 

[Jeff L]

The syntax for the query criteria would be:

Between #7/10/2006 14:00# And #7/12/2006 18:00#

Hope that helps!

 

[Douglas J. Steele]

The biggest problem is that you've got separate date and time fields.
Combined them into one, and it becomes easy.

If you can't (or won't) combine them permanently in the table (remember, you
can always use DateValue or TimeValue when you only want the date or only
want the time portion), try:

WHERE ([Start Day] + [Start Time]) BETWEEN #07/10/2006 14:00:00# AND
#07/12/2006 18:00:00#

Your other option would be the ugly:

WHERE (([Start Day] = #07/10/2006# AND [Start Time] >= #14:00:00#)
OR [Start Day] = #07/11/2006#
OR ([Start Day] = #07/12/2006# AND [Start Time] <= #18:00:00#))

 

[kev100 via AccessMonster.com]

And....I should have mentioned this earlier.......

This is to be done with User INPUT......the 7/10 and 7/12 Dates/Times are
used as examples...

 

[kev100 via AccessMonster.com]

Thanks very much.....

Since this would be with User Input (variables...) would something like:


WHERE ([Start Day] + [Start Time]) BETWEEN #[UserInputStartDate]
[UserInputStartTime]# AND
#[UserInputEndDate] [UserInputEndTime]#

....work ?

Thanks

 

[Douglas J. Steele]

Have you tried it?

Actually, I suspect you might have to use

WHERE ([Start Day] + [Start Time]) BETWEEN (CDate([UserInputStartDate]) +
CDate([UserInputStartTime])) AND
(CDate([UserInputEndDate]) + CDate([UserInputEndTime]))

 

[kev100 via AccessMonster.com]

Not yet.....but soon will.

You're prob. right concerning CDate.....it's being used in the current query
so that the time value can be properly evaluated.

 

[kev100 via AccessMonster.com]

Doug,

Thanks VERY much.....

I even added an Nz default entry function provided by Allen and Duane...
http://www.accessmonster.com/Uwe/Fo...a-Query-PROMPT-Criteria-type#62d8bdc2cf5bduwe

....to make it super user friendly.

It's now....

WHERE ((([StartDay]+[StartTIME]) Between (CDate(Nz([Enter FROM Date as mm/dd
(press ENTER for today)],Date()))+CDate(Nz([Enter FROM Time as hh:mm am/pm
(press ENTER for entire day):],"1 AM"))) And (CDate(Nz([TO Date (ENTER for
Today)],Date()))+CDate(Nz([TO Time (ENTER for Entire Day)],"11:49 PM")))))

Hard to read....but it works.

THANKS !