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

 

[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(2003,9,10)+TimeSerial(22,0,0))
And (DateSerial(2003,9,11))+TimeSerial(7,30,0));

HTH - Peter

 

[Lapchien]

Thanks - but how can the date ranges be changed within the query as a
parameter?

 

[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()+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.

 

[Peter Doering]

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

 

[Peter Doering]

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

 

[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!

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

 

[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))

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

 

[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

 

[John Viescas]

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)

= 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)

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!

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

 

[Lapchien]

Using it just as you say - but getting the 'This expression is typed
incorrectly... message...

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

 

[Peter Doering]

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

 

[John Viescas]

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)

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

 

[Peter Doering]

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

 

[Lapchien]

What do I enter in the Field area on the design grid?

 

[John Viescas]

From an earlier post:

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

= 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)

What do I enter in the Field area on the design grid?


--
Thanks,
Chris
(e-mail address removed)

Select * From YourTable

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

Or

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

And

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



HTH

 

[Lapchien]

Thanks for your help.

Lap


--
Thanks,
Chris
(e-mail address removed)

From an earlier post:

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

= 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)

What do I enter in the Field area on the design grid?


--
Thanks,
Chris
(e-mail address removed)

Select * From YourTable

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

Or

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

And

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



HTH