date/time query help

[Lapchien]

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****@NOSPAMec lipse.co.uk

[Peter Doering]

> 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(200 3,9,10)+TimeSer ial(22,0,0))
And (DateSerial(200 3,9,11))+TimeSe rial(7,30,0));

HTH - Peter

--
No mails please.

[Lapchien]

Thanks - but how can the date ranges be changed within the query as a
parameter?
--
Thanks,
Chris
cc****@NOSPAMec lipse.co.uk

"Peter Doering" <ne**@doering.o rg> 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(200 3,9,10)+TimeSer ial(22,0,0))
And (DateSerial(200 3,9,11))+TimeSe rial(7,30,0));

HTH - Peter

--
No mails please.

[Douglas J. Steele]

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()+TimeSer ial(22,0,0))
And (DateAdd("d", 1, Date())+TimeSer ial(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****@NOSPAMe clipse.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****@NOSPAMec lipse.co.uk

"Peter Doering" <ne**@doering.o rg> 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(200 3,9,10)+TimeSer ial(22,0,0))
And (DateSerial(200 3,9,11))+TimeSe rial(7,30,0));

HTH - Peter

--
No mails please.

[Speculator]

..... WHERE format(YourDate TimeField, "yyyymmddhh nn") Between
format(Date1 + #22:00#,"yyyymm ddhhnn") AND format(Date2 +
#07:00#,"yyyymm ddhhnn")
On Wed, 10 Sep 2003 19:38:42 +0100, "Lapchien"
<cc****@NOSPAMe clipse.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

[Peter Doering]

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(Yea r([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(1 0,0,0))
And (DateSerial(Yea r([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.

[Peter Doering]

> Between (DateSerial(Yea r([MyDate]),Month([MyDate]),

Day([MyDate]))+TimeSerial(1 0,0,0))
And (DateSerial(Yea r([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.

[Lapchien]

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.o rg> wrote in message
news:bj******** ****@ID-204768.news.uni-berlin.de...

Between (DateSerial(Yea r([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(1 0,0,0))
And (DateSerial(Yea r([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.

[Lapchien]

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.o rg> wrote in message
news:bj******** ****@ID-204768.news.uni-berlin.de...

Between (DateSerial(Yea r([MyDate]),Month([MyDate]),
Day([MyDate]))+TimeSerial(1 0,0,0))
And (DateSerial(Yea r([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.

[Peter Doering]

> 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.