a query to search time and date

[Lost]

I need to have a user defined query where the user will pick two date for a
search for between (date 1) and (date 2) but only put out information for
date with the time between 11:30am and 5:00pm.

The search is in a general date field named open time.

Thank You Very Much in advanced.

 

[KARL DEWEY]

In design view use two fields --
[open time] with criteria Between [Enter start date - m/d/yyyy] AND [Enter
end date - m/d/yyyy]
Calculated field --
TimeOfOpen: ([open time])-Int([open time])
with criteria on same row as for date --
Between #11:30 am# AND #5:00 pm#

 

[MGFoster]

I need to have a user defined query where the user will pick two date for a
search for between (date 1) and (date 2) but only put out information for
date with the time between 11:30am and 5:00pm.

The search is in a general date field named open time.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure of your criteria, but something like this....

PARAMETERS StartDate Date, EndDate Date;
SELECT <column name list>
FROM table_name
WHERE [open time] BETWEEN StartDate + #11:30# And EndDate + #17:00#

Or the WHERE clause could look like this:

WHERE [open time] BETWEEN StartDate And EndDate
AND TimeValue([open time]) BETWEEN #11:30:00# And #17:00:00#

The TimeValue() function returns the time of the date/time parameter.
Using this in a WHERE clause slows down the query 'cuz the query will
have to evaluate all rows to determine if the time is between 11:30 and
17:00 - (actually, it depends on the query optimizer - if it only tests
rows that meet the first criteria "Between StartDate And EndDate" it
will be faster). But, it makes the query more accurate 'cuz each
selected date will be between 11:30am and 5pm.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSZzu9IechKqOuFEgEQKRIwCeKZwc5CF4hh4Z1//qfT2xBNlqsMsAoJrO
9HUE/xE1R6/JlPpqTvsBeUPZ
=5BTb
-----END PGP SIGNATURE-----

 

[Lost]

Thank You very Much

Worked Perfectly

In design view use two fields --
[open time] with criteria Between [Enter start date - m/d/yyyy] AND [Enter
end date - m/d/yyyy]
Calculated field --
TimeOfOpen: ([open time])-Int([open time])
with criteria on same row as for date --
Between #11:30 am# AND #5:00 pm#

--
KARL DEWEY
Build a little - Test a little

I need to have a user defined query where the user will pick two date for a
search for between (date 1) and (date 2) but only put out information for
date with the time between 11:30am and 5:00pm.

The search is in a general date field named open time.

Thank You Very Much in advanced.