By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

date/time query help

P: n/a
My table has a record called date/time - filled (not surprisingly) with a
date and time. I can format the date in one query field, also the time,
using format in an expression. My user wants me to provide a listing of all
table entries between 22:00 and 07:30 the next day, between a given set of
dates (typically one week apart but in practice between any dates
specified).

My query works ok for 1 date - entering 2 dates, one for 22:00 - 23:59 and
another for 00:00 - 07:30, but for a whole date range... Any help please
guys?

Lap
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk
Nov 12 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
> My table has a record called date/time - filled (not surprisingly) with a
date and time. I can format the date in one query field, also the time,
using format in an expression. My user wants me to provide a listing of all
table entries between 22:00 and 07:30 the next day, between a given set of
dates (typically one week apart but in practice between any dates
specified). My query works ok for 1 date - entering 2 dates, one for 22:00 - 23:59 and
another for 00:00 - 07:30, but for a whole date range... Any help please
guys?

SELECT YourFields FROM YourTable WHERE YourDateTime
Between (DateSerial(2003,9,10)+TimeSerial(22,0,0))
And (DateSerial(2003,9,11))+TimeSerial(7,30,0));

HTH - Peter

--
No mails please.
Nov 12 '05 #2

P: n/a
Thanks - but how can the date ranges be changed within the query as a
parameter?
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"Peter Doering" <ne**@doering.org> wrote in message
news:bj************@ID-204768.news.uni-berlin.de...
My table has a record called date/time - filled (not surprisingly) with a date and time. I can format the date in one query field, also the time,
using format in an expression. My user wants me to provide a listing of all table entries between 22:00 and 07:30 the next day, between a given set of dates (typically one week apart but in practice between any dates
specified).

My query works ok for 1 date - entering 2 dates, one for 22:00 - 23:59 and another for 00:00 - 07:30, but for a whole date range... Any help please
guys?

SELECT YourFields FROM YourTable WHERE YourDateTime
Between (DateSerial(2003,9,10)+TimeSerial(22,0,0))
And (DateSerial(2003,9,11))+TimeSerial(7,30,0));

HTH - Peter

--
No mails please.

Nov 12 '05 #3

P: n/a
What should the day be? If it should refer to between 22:00 today and 07:30
tomorrow, you could try something like:

SELECT YourFields FROM YourTable WHERE YourDateTime
Between (Date()+TimeSerial(22,0,0))
And (DateAdd("d", 1, Date())+TimeSerial(7,30,0));

Otherwise, you're going to have to have your users input a date.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
Thanks - but how can the date ranges be changed within the query as a
parameter?
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"Peter Doering" <ne**@doering.org> wrote in message
news:bj************@ID-204768.news.uni-berlin.de...
My table has a record called date/time - filled (not surprisingly) with
a
date and time. I can format the date in one query field, also the
time, using format in an expression. My user wants me to provide a listing of
all table entries between 22:00 and 07:30 the next day, between a given
set
of dates (typically one week apart but in practice between any dates
specified).

My query works ok for 1 date - entering 2 dates, one for 22:00 - 23:59 and another for 00:00 - 07:30, but for a whole date range... Any help

please guys?

SELECT YourFields FROM YourTable WHERE YourDateTime
Between (DateSerial(2003,9,10)+TimeSerial(22,0,0))
And (DateSerial(2003,9,11))+TimeSerial(7,30,0));

HTH - Peter

--
No mails please.


Nov 12 '05 #4

P: n/a

..... WHERE format(YourDateTimeField, "yyyymmddhhnn") Between
format(Date1 + #22:00#,"yyyymmddhhnn") AND format(Date2 +
#07:00#,"yyyymmddhhnn")
On Wed, 10 Sep 2003 19:38:42 +0100, "Lapchien"
<cc****@NOSPAMeclipse.co.uk> wrote:
My table has a record called date/time - filled (not surprisingly) with a
date and time. I can format the date in one query field, also the time,
using format in an expression. My user wants me to provide a listing of all
table entries between 22:00 and 07:30 the next day, between a given set of
dates (typically one week apart but in practice between any dates
specified).

My query works ok for 1 date - entering 2 dates, one for 22:00 - 23:59 and
another for 00:00 - 07:30, but for a whole date range... Any help please
guys?

Lap


Nov 12 '05 #5

P: n/a
On Wed, 10 Sep 2003 22:23:54 +0100, Lapchien wrote:
Thanks - but how can the date ranges be changed within the query as a
parameter?


What are you entering? 1 date or both dates, time as well or is it fix? I
assume you enter one date and calculate the next day:

- In your query go to menu query - parameters and create a parameter called
MyDate, type Date/Time.
- Use following syntax in the WHERE clause:

Between (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(10,0,0))
And (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+1+TimeSerial(6,0,0))

If the example isn't quite right you have to play around with the
variables, e.g. create a 2nd parameter in case you want to enter 2 dates,
or a 3rd and 4th for the times.

Peter

--
No mails please.
Nov 12 '05 #6

P: n/a
> Between (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(10,0,0))
And (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+1+TimeSerial(6,0,0))


That was too fast:

Between ([MyDate]+TimeSerial(10,0,0)) And ([MyDate]+1+TimeSerial(7,30,0))

will do too. ;-)

Peter

--
No mails please.
Nov 12 '05 #7

P: n/a
hmm okay - maybe I can add a bit more info. The query needs to report a
listing of all entries to the db between 22:00 and 07:30 the next day, over
a given week, or 2 days, or a month - whatever the 'start date' and 'end
date' might be, depending on who wants the report. I assume that the user
will have to enter these 2 dates...

Thanks so far guys!
"Peter Doering" <ne**@doering.org> wrote in message
news:bj************@ID-204768.news.uni-berlin.de...
Between (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(10,0,0))
And (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+1+TimeSerial(6,0,0))


That was too fast:

Between ([MyDate]+TimeSerial(10,0,0)) And ([MyDate]+1+TimeSerial(7,30,0))

will do too. ;-)

Peter

--
No mails please.

Nov 12 '05 #8

P: n/a
This is what I have (but returns no records...)

Between [first date] And [second date] And Between (+TimeSerial(22,0,0)) And
(+TimeSerial(7,30,0))

"Peter Doering" <ne**@doering.org> wrote in message
news:bj************@ID-204768.news.uni-berlin.de...
Between (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(10,0,0))
And (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+1+TimeSerial(6,0,0))


That was too fast:

Between ([MyDate]+TimeSerial(10,0,0)) And ([MyDate]+1+TimeSerial(7,30,0))

will do too. ;-)

Peter

--
No mails please.

Nov 12 '05 #9

P: n/a
> This is what I have (but returns no records...)
Between [first date] And [second date] And Between (+TimeSerial(22,0,0)) And
(+TimeSerial(7,30,0))


But this is not what I suggested. With this clause you'll get e.g.:

Between #9/10/2003# And #9/11/2003# And Between #12/30/1899 22:00:00# And
#12/30/1899 07:30:00#

So it won't return any records.

With this clause...

Between ([first date] + TimeSerial(22,0,0))
And ([second date] + TimeSerial(7,30,0))

.... you'll get:
Between #9/10/2003 22:00:00# And #9/11/2003 07:30:00#
HTH - Peter

--
No mails please.
Nov 12 '05 #10

P: n/a
PMJI-

Let's say your field name is LogDateTime. The following SQL WHERE clause
should work:

WHERE LogDateTime >= [Enter Start Date:]
AND LogDateTime <= ([Enter End Date:] + 1) + TimeValue(7,30,0)
AND ((TimeValue([LogDateTime]) >= TimeValue(22,0,0))
OR (TimeValue([LogDateTime]) <= TimeValue(7,30,0)))

This finds all the records in the date span you want, and then returns only
the ones that occur between 22:00 and 07:30 on those days. This is tricky
to enter on the design grid. Create a calculated field Expr1: TimeValue([ <
name of your field here >]). Under the unaltered field on the query grid,
put the following on *both* the Criteria and the first Or row:
= [Enter Start Date:] AND <= ([Enter End Date:]+1) + TimeValue(7,30,0)
On the Criteria row under your new expression, enter:
= TimeValue(22,0,0)
On the Or row under your new expression, enter:

<= TimeValue(7,30,0)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Lapchien" <cc****@nospameclipse.co.uk> wrote in message
news:10**************@ananke.eclipse.net.uk... hmm okay - maybe I can add a bit more info. The query needs to report a
listing of all entries to the db between 22:00 and 07:30 the next day, over a given week, or 2 days, or a month - whatever the 'start date' and 'end
date' might be, depending on who wants the report. I assume that the user
will have to enter these 2 dates...

Thanks so far guys!
"Peter Doering" <ne**@doering.org> wrote in message
news:bj************@ID-204768.news.uni-berlin.de...
Between (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(10,0,0))
And (DateSerial(Year([MyDate]),Month([MyDate]),
Day([MyDate]))+1+TimeSerial(6,0,0))


That was too fast:

Between ([MyDate]+TimeSerial(10,0,0)) And ([MyDate]+1+TimeSerial(7,30,0))
will do too. ;-)

Peter

--
No mails please.


Nov 12 '05 #11

P: n/a
Using it just as you say - but getting the 'This expression is typed
incorrectly... message...


"Peter Doering" <ne**@doering.org> wrote in message
news:bj************@ID-204768.news.uni-berlin.de...
This is what I have (but returns no records...)

Between [first date] And [second date] And Between (+TimeSerial(22,0,0)) And (+TimeSerial(7,30,0))


But this is not what I suggested. With this clause you'll get e.g.:

Between #9/10/2003# And #9/11/2003# And Between #12/30/1899 22:00:00# And
#12/30/1899 07:30:00#

So it won't return any records.

With this clause...

Between ([first date] + TimeSerial(22,0,0))
And ([second date] + TimeSerial(7,30,0))

... you'll get:
Between #9/10/2003 22:00:00# And #9/11/2003 07:30:00#
HTH - Peter

--
No mails please.

Nov 12 '05 #12

P: n/a
> Using it just as you say - but getting the 'This expression is typed
incorrectly... message...
Between ([first date] + TimeSerial(22,0,0))
And ([second date] + TimeSerial(7,30,0))


Did you also follow the point in my posting a bit further up?
- In your query go to menu query - parameters and create a
parameter called MyDate, type Date/Time.


Now there should be 2 parameters:

First Date (Date/Time)
Second Date (Date/Time).

HTH - Peter

--
No mails please.
Nov 12 '05 #13

P: n/a
Peter-

Please see my reply earlier in the thread. I think what the poster wants is
all the records that occur ONLY between 10p and 7:30a between two dates. In
other words, "show me the total hours on the graveyard shift between
September 1 and September 9 (including the morning of September 10)." Your
suggested predicate returns all records between 10p on the start date and
7:30a on the end date - which might also include records later than 7:30a or
earlier than 10p.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Peter Doering" <ne**@doering.org> wrote in message
news:bj************@ID-204768.news.uni-berlin.de...
Using it just as you say - but getting the 'This expression is typed
incorrectly... message...

Between ([first date] + TimeSerial(22,0,0))
And ([second date] + TimeSerial(7,30,0))


Did you also follow the point in my posting a bit further up?
- In your query go to menu query - parameters and create a
parameter called MyDate, type Date/Time.


Now there should be 2 parameters:

First Date (Date/Time)
Second Date (Date/Time).

HTH - Peter

--
No mails please.

Nov 12 '05 #14

P: n/a
John,
Please see my reply earlier in the thread. I think what the poster wants is
all the records that occur ONLY between 10p and 7:30a between two dates. In
other words, "show me the total hours on the graveyard shift between
September 1 and September 9 (including the morning of September 10)." Your
suggested predicate returns all records between 10p on the start date and
7:30a on the end date - which might also include records later than 7:30a or
earlier than 10p.


I just cross-read these 2 postings and missed this tiny bit of information
;-)

When the poster has entered the 2 dates as query parameters of type
date/time your suggested solution will work.

Peter

--
No mails please.
Nov 12 '05 #15

P: n/a
What do I enter in the Field area on the design grid?
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"DannyY" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

Select * From YourTable

Where (TimeValue(YourDateAndTimeField)>TimeValue("22:00" )

Or

TimeValue(YourDateAndTimeField)<TimeValue("07:30") )

And

(DateValue(YourDateAndTimeField) Between [BeginningDate] And [EndDate])

HTH
--
Posted via http://dbforums.com

Nov 12 '05 #16

P: n/a
From an earlier post:

This is tricky to enter on the design grid. Create a calculated field
Expr1: TimeValue([ < name of your field here >]).

On the Criteria row under your new expression, enter:
= TimeValue(22,0,0)
On the Or row under your new expression, enter:

<= TimeValue(7,30,0)

Under the unaltered field on the query grid,
put the following on *both* the Criteria and the first Or row:
= [Enter Start Date:] AND <= ([Enter End Date:]+1) + TimeValue(7,30,0)
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk... What do I enter in the Field area on the design grid?
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"DannyY" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

Select * From YourTable

Where (TimeValue(YourDateAndTimeField)>TimeValue("22:00" )

Or

TimeValue(YourDateAndTimeField)<TimeValue("07:30") )

And

(DateValue(YourDateAndTimeField) Between [BeginningDate] And [EndDate])

HTH
--
Posted via http://dbforums.com


Nov 12 '05 #17

P: n/a
Thanks for your help.

Lap
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"John Viescas" <Jo***@nomail.please> wrote in message
news:Oo**************@TK2MSFTNGP12.phx.gbl...
From an earlier post:

This is tricky to enter on the design grid. Create a calculated field
Expr1: TimeValue([ < name of your field here >]).

On the Criteria row under your new expression, enter:
= TimeValue(22,0,0)


On the Or row under your new expression, enter:

<= TimeValue(7,30,0)

Under the unaltered field on the query grid,
put the following on *both* the Criteria and the first Or row:
= [Enter Start Date:] AND <= ([Enter End Date:]+1) + TimeValue(7,30,0)


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
What do I enter in the Field area on the design grid?
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"DannyY" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

Select * From YourTable

Where (TimeValue(YourDateAndTimeField)>TimeValue("22:00" )

Or

TimeValue(YourDateAndTimeField)<TimeValue("07:30") )

And

(DateValue(YourDateAndTimeField) Between [BeginningDate] And [EndDate])
HTH
--
Posted via http://dbforums.com



Nov 12 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.