Query with time range across multiple days.

[Rockeiro]

My first post! Glad to have found such an exceptional forum.

I am trying to figure out how to get a query that was designed to show data from 1 or more days from time 1 to time 2 to show data from date 1, time 1 to date 2, time 2 and I'm stumped.

I got as far as getting day 1, time 1 to day 2 (end of day) but I can't seem to get the second day's hour to work. As soon as I add this in to the criteria, I get a blank.

The query is pulling in data from 3 tables with linked primary values. The date field is from one table and the hour is from the other. The application is to change a shift report at a plant from reporting the 24 hours in a single day to report from 6 am one day to 6 am the next day. Of course we will expand it once we get this running to virtual any time period they want but I have to get past the first step.

The pre-existing criteria was:
date field criteria: >=[Forms]![DATE DEFAUT]![date de début] And <=[Forms]![DATE DEFAUT]![date de fin]
HEURE field criteria: >=[Forms]![DATE DEFAUT]![heure de début] And <=[Forms]![DATE DEFAUT]![heure de fin]

These criteria of course will work on multiple days but only for a fixed range of time within each day, not across multiple days. Also, I was using fixed dates and times in place of these variables to get the results I spoke of earlier. The variables above are coming from a form with unbound fields when the print report button is pushed and passes the variable data to the report that calls the query I'm working on.

Is it possible to get this data out with just simple criteria (I'm doubting it is) and if not what is your opinion about the best methodology to use to change this. If it's just a criteria thing I've missed, what am I not doing correctly?

 

[G81]

Welcome to the forum.

I think you need to combine the date and time fields. Like so:

combineddatetime: [mydatefield] & " " & [mytimefield]

and then run the full criteria. Does that help?

 

[MarkK]

Hey there, and welcome to the forum.
Time is a variable quantity like money or distance or height. This being the case, it seems suspicious that you draw days from one table and hours from another.
A day is not a distinct thing in respect to time. It seems like it because it gets dark at night and so on. Everybody sleeps.
But days are to hours what dollars are to cents and what feet are to inches, namely, the same thing. Then to restrict records based on days in one table and hours in another seems wrong to me.
Maybe I don't understand the problem correctly, but consider what I'm saying about time. Date and time are ONE thing.
HTH

 

[Rockeiro]

G81:
Was thinking myself that a sub-query might be the best answer although joining the fields was something I wondered about too. I'm not a db guru; I'm an industrial PLC programmer so this is not my forte.

lagbolt:
valid comments about the architecture but it's what I have to work with as I didn't design the application; I just have to make it work with the least amount of pain.

I would have data and time stamped the thing if it was my app right from the start by the way as I agree with both points of view that time is one commodity, even if dates and time have different units.

 

[MarkK]

So can't you include this as a single where clause in your query, combining the date and hour comparisons, like ...

WHERE YourDate >=[Forms]![DATE DEFAUT]![date de début] 
AND YourDate <=[Forms]![DATE DEFAUT]![date de fin]
AND YourHour >=[Forms]![DATE DEFAUT]![heure de début]
AND YourHour <=[Forms]![DATE DEFAUT]![heure de fin]

But to get more help you may have to post more information. There are so many things that can have an impact on how a query returns records, and some of descriptions of the problem could be more specific ...

I can't seem to get the second day's hour to work. As soon as I add this in to the criteria, I get a blank.

A few questions that jump to mind: What is the datatype of "second day's hour"? How do you add this to the criteria, like show the exact syntax you use that works and the exact syntax that fails. Always useful if you can post a database so people here can work with an actual thing than speculate from a description.
Cheers,

 

[Rockeiro]

You can see from my original post the original criteria that worked, but not the way I wanted it to.

Here's the criteria I was playing with that have hard dates and time that don't work either. I get a blank query.
date criteria: >=#7/12/2010# And <=#7/13/2010#
time criteria: >=#6:00:00 AM# And <=#5:59:00 AM#
It's pretty easy to see why this won't work.

I had thought that I could leave the date criteria blank but in the time criteria that I would like to refer to the time based on the date and that would work but the syntax escapes me. I actually tried but it asked me what the heck field "date" was. Hmm..

Can I just have the criteria in the time field that can refer back over to the date field?
pseudo time criteria: >= 06:00 on (date 7/12/2010) AND < =06:00 on (date 7/13/2010)

I would love to send the db if you think you can work on an Access 2.0 db.

 

[MarkK]

Yeah, depending on how the data's structured it might not be possible. Post the DB if you want. Not sure if 2007 still supports importing from 2.0 but who knows.
Cheers,

 

[Rockeiro]

Here it is. It's the BAVARD query that I'm working on. It won't import into my Access 2007 so I working on it inside a W2k VM running Access 2.0

Maybe you can open it?

Here is a heads up on how the report is being generated:

Report
FORM - DATE DEFAUT ANCIEN
opens (on open) Function DateDefaut to set language
loads (on load) FORM DATE DEFAUT
FORM - DATE DEFAUT
opens (on open) Function DateDefaut to set language
on load MACRO6

Buttons on form
Print Full Report Button - Sub Button17_Click() Report BAVARDA
Print Totals Only Report - Sub Button18_Click() Report BAVARDT

Reports
BAVARDA - Record source BAVARD (Query)
BAVARDT - Record source BAVARD (Query)
date field criteria: >=[Forms]![DATE DEFAUT]![date de début] And <=[Forms]![DATE DEFAUT]![date de fin]
HEURE field criteria: >=[Forms]![DATE DEFAUT]![heure de début] And <=[Forms]![DATE DEFAUT]![heure de fin]

 

[MarkK]

Here, run this query ...

SELECT t1.date, t1.heure, t2.HEURE, Abs(Round(CDate([t1].[heure])-CDate([t2].[heure]),4)) AS Difference
FROM DDEfromProw AS t1 INNER JOIN DETDDEfromProw AS t2 ON t1.BON = t2.BON
WHERE (((Abs(Round(CDate([t1].[heure])-CDate([t2].[heure]),4)))>0.001));

There is a time field in DDEfromProw and a time in field in DETDDEfromProw. When the tables are joined this query shows that the times already totally agree with each other. If you remove the '>0.001' criteria, you'll see the calculated difference between the times in the two tables. Very small. This means that you can query the date and time from a single table which will solve your problem.
If you run this query, it shows how to recombine the date and time to a single value ...

SELECT DDEfromProw.date, DDEfromProw.heure, [date]+CDate([heure]) AS ReCombined
FROM DDEfromProw;

Bon chance,

 

[Rockeiro]

Access 2.0 says "Undefined function "Round" in expression".

I think I see where you're going with this. As you can see, second accuracy however is all that's needed and that why you're rounding. Can you just cut off (ignore) the 10ths of seconds instead of rounding up?

BY the way, how did you open it up on Access 2007? I couldn't get it to work for me. Said I had to load it into an older version first.

And thanks for the Bon Chance! It was guys from Quebec that wrote this originally but I'm an Alberta boy with Babylon to get me through the French!!

 

[MarkK]

Ahh, thought you might be in Quebec. I grew up in Montreal.

From an Access 2.0 DB you can import tables, queries and modules into 2007. However, it fails if you try to open the Access 2.0 file directly.

And all I'm doing in the first query is proving that for the data you posted the time in DETDDE is--except for the seconds--the same as the time in DDE. I want to prove this so you can rely on the time in DDE and then your date and your time are in the same record, which simplifies things.
But it just occurred to me too, you can do this ....

WHERE 
  ( DDEfromProw.date >=[Forms]![DATE DEFAUT]![date de début] 
  AND CDate(DETDDEfromProw.heure) >=[Forms]![DATE DEFAUT]![heure de début] )
AND 
  ( DDEfromProw.date <=[Forms]![DATE DEFAUT]![date de fin]
  AND CDate(DETDDEfromProw.heure) <=[Forms]![DATE DEFAUT]![heure de fin] )

You can nest the ANDs. There still might be a datatype problem with the times, but this might answer the question posed as per your original post.
Cheers,

 

[Rockeiro]

Unfortunately there is no CDATE or ROUND in Access 2.0.

Maybe I'm hooped?

 

[MarkK]

You don't NEED Round() or CDate().
I used Round() to establish that the time data in the two tables was the same. It is.
I've used CDate() a couple of times to make sure I'm returning a date datatype, but this is not essential. I presumed this reference would return a date ...

[Forms]![DATE DEFAUT]![heure de début]

... but that was just a guess. Maybe you can do a valid string comparison here.
Maybe post the query text you're working on. I've got the tables somewhere...
Cheers,