Date Comparison Where Midnight is involved 1

[Accel45]

I have a table "tblDocs" with the following date/time fields

WDate (Date Only) 1/16/2009
TimeIn (Time Only) 8:00 AM
EDate (Date Only) 1/16/2009
TimeOut (Time Only) 8:00 PM
StartDate (Date and Time) 1/16/2009 8:00:00 AM
EndDate (Date and Time) 1/16/2009 8:00:00 PM

In a query "qryTIMELINEGraph" I am comparing the TimeIn and TimeOut values against time periods in 24 hours like this:
Hr00,Hr0030,Hr01,Hr0130...

Hr01: IIf(#1:00:00 AM# And #1:30:00 AM# Between [TimeIn] And [TimeOut],"......"," ")

The result successfully creates a graph effect like this on a report.
8:00 AM ...................................... 8:00 PM
10:00 AM ............ 5:00 PM

My issue is that if Midnight, 00:00 is entered in TimeIn or TimeOut then the comparison in qryTIMELINEGraph fails.

I tried using StartDate and EndDate in place of TimeIn and TimeOut but that did not correct the problem. StartDate and EndDate contain the same data as WDate, TimeIn and EDate, TimeOut. I added StartDate and EndDate in an attempt to resolve the issue.

Any help correcting this issue will be appreciated.

Thank You
Accel45

 

[SkipVought]

hi,

Your arithmetic difference ought to use the EndDate, StartDate values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Accel45]

Thank you for your response.

I have tried using EndDate, StartDate Values without success.

 

[SkipVought]

HOW???

Please post what you have tried!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Accel45]

Hr01: IIf(#1:00:00 AM# And #1:30:00 AM# Between [StartDate] And [EndDate],"......"," ")

 

[SkipVought]

THAT is not calculating a TIME DIFFERENCE that could be used as a BAR LENGTH.

Of COURSE, a TIME ONLY value can NOT lie between two DATE/TIME values!!!

In this instance you need the TimeIn, TimeOutvalues. Does that not make sense?
[tt]
Hr01: IIf(#1:00:00 AM# <[EndDate] And #1:30:00 AM#>[StartDate],"......"," ")
[/tt]
But I'd use the TEXT() function in your query, to repeat the desirec character a proportional number of times to vary the bar length.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Accel45]

Skip,

Sorry, but I'm not sure I am following you.

I tried
Hr01: IIf(#1:00:00 AM# <[EndDate] And #1:30:00 AM#>[StartDate],"......"," ")
without success.

I tried
Hr01a: IIf(#1:00:00 AM#<[TimeOut] And #1:30:00 AM#>[TimeIn],"......"," ")
With mixed results.
It works if the value of TimeIn is 12:00:00 AM but it does not work if the value of TimeOut is 12:00:00 AM.

 

[SkipVought]

We need to so some sample data please

I find it difficlut to inderstand how you get...
[tt]
8:00 AM ...................................... 8:00 PM
10:00 AM ............ 5:00 PM
[/tt]
from...
[tt]
Hr01: IIf([highlight]#1:00:00 AM# And #1:30:00 AM#[/highlight] Between [StartDate] And [EndDate],"......"," ") [/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Accel45]

Hr01: IIf(#1:00:00 AM# And #1:30:00 AM# Between [StartDate] And [EndDate],"......"," ") is one column of 48 columns in qryTIMELINEGraph.

There is a column for each half hour of the day. Hr00,Hr0030,Hr01,Hr0130...

If TimeIn or TimeOut falls within one or more of the half hour periods then "......" is returned, If not the " " is returned.

Also with qryTIMELINEGraph are a series of columns/code that combine the "......" and " " to create the simulated timeline.

Slip1: [Hr00] & [Hr0030] & [Hr01] & [Hr0130] & [Hr02] & [Hr0230] & [Hr03] & [Hr0330] & [Hr04] & [Hr0430] & [Hr05] & [Hr0530] & [Hr06] & [Hr0630] & [Hr07] & [Hr0730] & [Hr08] & [Hr0830] & [Hr09] & [Hr0930] & [Hr10] & [Hr1030] & [Hr11] & [Hr1130] & [Hr12] & [Hr1230] & [Hr13] & [Hr1330] & [Hr14] & [Hr1430] & [Hr15] & [Hr1530] & [Hr16] & [Hr1630] & [Hr17] & [Hr1730] & [Hr18] & [Hr1830] & [Hr19] & [Hr1930] & [Hr20] & [Hr2030] & [Hr21] & [Hr2130] & [Hr22] & [Hr2230] & [Hr23] & [Hr2330]

Another column adds the TimeIn to the begining of Slip1 and TimeOut to the end of Slip1. Something like this:
Graph: [TimeIn] & [Slip1] & [TimeOut]


To get: 8:00 AM ...................................... 8:00 PM
This record has a TimeIN of 8:00 Am and a TimeOut of 8:00 PM


To get: 10:00 AM ............ 5:00 PM
This record has a TimeIn of 10:00 AM and a TimeOut of 5:00 PM

The dots and spaces give the appearance of the records stacking.
8:00 AM ...................................... 8:00 PM
10:00 AM ............ 5:00 PM
4:00 AM .................. 9:00 AM

In the report the records are grouped by date and look like a timeline. This is used to provide a visual of potential overlapping records (time periods).

The problem for me is that 00:00:00 midnight. Previously if there was a time period that ended on midnight I would just enter 11:59 PM. If the time period began on midnight I would enter 12:01 AM. This functioned as a work around but it is not an accurate reflection of the time period.

So today I am seeking help with this midnight problem. My Timeline works fine unless midnight is involved.

I do appreciate you taking time to help me with this.

 

[SkipVought]

Have you tried using a BAR chart, where the horizontal axis is Date/Time?

You have a Bar Start Date/Time and a Duration (End-Start).

Ends up like...
[tt]
8/30 00:00 8/30 12:00 8/31 00:00
+----+-----------+-----------+------
Item1 | ::::::::::::
Item2 | ::::::::::::::::::::

[/tt]
where
[tt]
Start End Duration
Item1 8/30/2011 01:00 8/30/2011 13:00 .5
Item2 8/30/2011 06:00 8/31/2011 01:00 .79
[/tt]
In your case, if you just want dots on a reportout, use the duration to determine the number (length) of dots. You can use a scale factor so it fits your imposed scale of 6 dots per hour or whatever.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Accel45]

I'm going to give this a try.
How would I go about using duration to determing the number of dots.

 

[SkipVought]

x dots per hour, is x dots per 1/24 means the product of 24, x and duration in days.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Accel45]

Skip,

Thank you for your help.
Using your suggestion regarding duration I was able to find a solution that appears to be working.

Not sure I understand the whole midnight issue yet.

Accel45

 

[SkipVought]

Because time in isolation from date is meaningless in this kind of process.

If you take ONLY time of 23:59, a value of 0.999305556, and add one minute, you do not get 0:00. Rather, you get Jan 1, 1900 0:00, a value of 1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}