Query Based on Time Rane and Date Range

[Little Penny]

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

 

[Crystal (strive4peace)]

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]

Thank you

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
*

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

 

[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

Thank you

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
*

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

 

[John W. Vinson]

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?

 

[Little Penny]

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


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]

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.

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?

 

[KenSheridan via AccessMonster.com]

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

My goal is to end up with a query where Ican use the DTpicker from a
form. Is this not possible?
[quoted text clipped - 6 lines]

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