Query Based on Time Rane and Date Range

How can I create a query that can searh for a time range

Example between 6pm and 11pm

base on a date ragnge


example 6/1/09 - 9/1/09



I want the query to return everthing between 6pm and 11pm from June
1st to stepember 1st. Is this possible?




Thanks




Little Penny

Hi Penny,

SELECT fieldlist
FROM [tablename]
WHERE DateValue([date_field]) BETWEEN #6/1/2009# AND #9/1/2009#
AND
TimeValue([date_field]) BETWEEN #18:00# AND #23:00#

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Little Penny wrote:
How can I create a query that can searh for a time range

Example between 6pm and 11pm

base on a date ragnge


example 6/1/09 - 9/1/09



I want the query to return everthing between 6pm and 11pm from June
1st to stepember 1st. Is this possible?




Thanks




Little Penny

Thank you



On Sat, 28 Nov 2009 19:39:55 -0700, "Crystal (strive4peace)"
wrote:

Hi Penny,

SELECT fieldlist
FROM [tablename]
WHERE DateValue([date_field]) BETWEEN #6/1/2009# AND #9/1/2009#
AND
TimeValue([date_field]) BETWEEN #18:00# AND #23:00#

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Little Penny wrote:
How can I create a query that can searh for a time range

Example between 6pm and 11pm

base on a date ragnge


example 6/1/09 - 9/1/09



I want the query to return everthing between 6pm and 11pm from June
1st to stepember 1st. Is this possible?




Thanks




Little Penny

I want to put this into a command button. What is the correct Syntax


DateValue([Create_DateTime]) BETWEEN Forms![Form1]![DateFrom] AND
Forms![Form1]![DateTo] AND TimeValue([Create_DateTime]) BETWEEN
Forms![Form1]![StrTime] AND Forms![Form1]![StrTime]


Thanks




On Sat, 28 Nov 2009 22:08:18 -0500, Little Penny
wrote:


Thank you



On Sat, 28 Nov 2009 19:39:55 -0700, "Crystal (strive4peace)"
wrote:

Hi Penny,

SELECT fieldlist
FROM [tablename]
WHERE DateValue([date_field]) BETWEEN #6/1/2009# AND #9/1/2009#
AND
TimeValue([date_field]) BETWEEN #18:00# AND #23:00#

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Little Penny wrote:
How can I create a query that can searh for a time range

Example between 6pm and 11pm

base on a date ragnge


example 6/1/09 - 9/1/09



I want the query to return everthing between 6pm and 11pm from June
1st to stepember 1st. Is this possible?




Thanks




Little Penny

On Sat, 28 Nov 2009 22:35:32 -0500, Little Penny
wrote:



I want to put this into a command button. What is the correct Syntax


DateValue([Create_DateTime]) BETWEEN Forms![Form1]![DateFrom] AND
Forms![Form1]![DateTo] AND TimeValue([Create_DateTime]) BETWEEN
Forms![Form1]![StrTime] AND Forms![Form1]![StrTime]

Wrong place. This is the WHERE clause of a Query; it is not VBA code, and it
is not something you can "put into a button".

Could you explain what you're trying to accomplish, and what's the context?
--

John W. Vinson [MVP]

My goal is to end up with a query where Ican use the DTpicker from a
form. Is this not possible?








On Sat, 28 Nov 2009 21:02:28 -0700, John W. Vinson
wrote:

On Sat, 28 Nov 2009 22:35:32 -0500, Little Penny
wrote:



I want to put this into a command button. What is the correct Syntax


DateValue([Create_DateTime]) BETWEEN Forms![Form1]![DateFrom] AND
Forms![Form1]![DateTo] AND TimeValue([Create_DateTime]) BETWEEN
Forms![Form1]![StrTime] AND Forms![Form1]![StrTime]

Wrong place. This is the WHERE clause of a Query; it is not VBA code, and it
is not something you can "put into a button".

Could you explain what you're trying to accomplish, and what's the context?

On Sat, 28 Nov 2009 23:08:58 -0500, Little Penny
wrote:


My goal is to end up with a query where Ican use the DTpicker from a
form. Is this not possible?

Since you have not yet mentioned a Form or a date picker or a query - and
since this option has nothing whatsoever to do with a command button - all I
can say "well, sure, it's possible, if that's what you meant to ask".

The expression Crystal posted is the WHERE clause of a Query. It assumes that
you have a Form named Form1 with three textboxes, named DateFrom, DateTo and
StrTime.

There's no reason whatsoever that you couldn't use a DTPicker control instead
of DateFrom, and another one instead of DateTo. The DTPicker control, however,
doesn't (to my knowledge) include a time picker.








On Sat, 28 Nov 2009 21:02:28 -0700, John W. Vinson
wrote:

On Sat, 28 Nov 2009 22:35:32 -0500, Little Penny
wrote:



I want to put this into a command button. What is the correct Syntax


DateValue([Create_DateTime]) BETWEEN Forms![Form1]![DateFrom] AND
Forms![Form1]![DateTo] AND TimeValue([Create_DateTime]) BETWEEN
Forms![Form1]![StrTime] AND Forms![Form1]![StrTime]

Wrong place. This is the WHERE clause of a Query; it is not VBA code, and it
is not something you can "put into a button".

Could you explain what you're trying to accomplish, and what's the context?
--

John W. Vinson [MVP]

While there is no built in 'Time Picker' you could add two unbound combo
boxes, cboTimeFrom and cboTimeTo, to the form and fill them with code in the
form's Open event procedure. To list all times from midnight until 11:45 PM
in 15 minute steps for instance the code would be:

Dim dtmTime As Date
Dim dblInterval As Double

dblInterval = 1 / (24 * 4)

Me.cboTimeFrom.RowSourceType = "Value List"
Me.cboTimeTo.RowSourceType = "Value List"

For dtmTime = #12:00:00 AM# To #11:45:00 PM# Step dblInterval
Me.cboTimeFrom.AddItem Format(dtmTime, "hh:nn AM/PM")
Me.cboTimeTo.AddItem Format(dtmTime, "hh:nn AM/PM")
Next dtmTime

Your Query would then be like this:

PARAMETERS
Forms![Form1]![DateFrom] DATETIME,
Forms![Form1]![DateTo] DATETIME,
Forms![Form1]![cboTimeFrom] DATETIME,
Forms![Form1]![cboTimeTo] DATETIME;
SELECT *
FROM [YourTable]
WHERE DATEVALUE([YourDateField])
BETWEEN Forms![Form1]![DateFrom]
AND Forms![Form1]![DateTo]
AND TIMEVALUE([YourDateField])
BETWEEN Forms![Form1]![cboTimeFrom]
AND Forms![Form1]![cboTimeTo];
ORDER BY [YourDateField];

You can either open another form with this query as its RecordSource property
from a button on Form1, or you can make the RecordSource of Form1 the above
query and include controls bound to the fields returned by the query on the
form, in which case you'd just requery the form in the button's Click event
procedure with:

Me.Requery

Ken Sheridan
Stafford, England

Little Penny wrote:
My goal is to end up with a query where Ican use the DTpicker from a
form. Is this not possible?

I want to put this into a command button. What is the correct Syntax

[quoted text clipped - 6 lines]

Could you explain what you're trying to accomplish, and what's the context?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200911/1