So Close, but...

[red skelton via AccessMonster.com]

Hi everyone,
I have been working on a query for the past couple of weeks and thanks to
everyone who has given me suggestions on how to work it. The problem is that
my elapsed time is sometimes off by 1 minute and I cant figure out why and is
not consistent. Let me show you my the result;

Time out of OR Next OR Time Elapsed Time
7:41 7:56 15 minutes
(correct)
8:20 8:45 24 minutes
(1 minute off)
9:33 9:54 21 minutes
(correct)
10:32 11:01 28 minutes (1
minute off)
11:56 12:18 22 minutes
(correct)

As you can see, its driving me a bit mad :>/ I have included my sql
statements as well. If anyone can make sense out of this, please help me
retain my sanity :>) I am using a pair of nested queries.

Query 1

SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.Name, Table1.Prefix,
Table1.SSN, Table1.[UCA Code], Table1.Surgeon, Table1.Procedure, Table1.
TimeOutOfOR, Int((Table1_Dup!TimeInOR-Table1!TimeOutOfOR)*24*60) AS Elapsed,
Table1_Dup.TimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup ON Table1.Date = Table1_Dup.Date
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[enter OR#]) AND ((Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]
) AND ((Table1_Dup.[OR#])=[Table1]![OR#]))
ORDER BY Table1.Date, Table1_Dup.[OR#];

Query 2

SELECT BetweenTimesQuery1.ID AS Expr1, BetweenTimesQuery1.Date,
BetweenTimesQuery1.[OR#], BetweenTimesQuery1.TimeOutOfOR, Min
(BetweenTimesQuery1.Elapsed) AS [ElapsedTimeBetween TimeoutORandNextTimeInOR]
FROM BetweenTimesQuery1
GROUP BY BetweenTimesQuery1.ID, BetweenTimesQuery1.Date, BetweenTimesQuery1.
[OR#], BetweenTimesQuery1.TimeOutOfOR
HAVING (((BetweenTimesQuery1.[OR#])=[enter OR#]))
ORDER BY BetweenTimesQuery1.TimeOutOfOR;

Any help would be greatly appreciated.
VR,
Red

 

[OfficeDev18 via AccessMonster.com]

I think the problem is that your time fields are pure numeric instead of
actual time fields, unless I'm way off base. I think if you change the time
in and out fields to real time fields, and calculate the elapsed time by
adding or subtracting time, your problem will go away.

Sam

ps red skelton, eh? Is he still on TV? He was on when I was a kid, and I'm
almost 57 now. I guess we're both showing our age!

Hi everyone,
I have been working on a query for the past couple of weeks and thanks to
everyone who has given me suggestions on how to work it. The problem is that
my elapsed time is sometimes off by 1 minute and I cant figure out why and is
not consistent. Let me show you my the result;

Time out of OR Next OR Time Elapsed Time
7:41 7:56 15 minutes
(correct)
8:20 8:45 24 minutes
(1 minute off)
9:33 9:54 21 minutes
(correct)
10:32 11:01 28 minutes (1
minute off)
11:56 12:18 22 minutes
(correct)

As you can see, its driving me a bit mad :>/ I have included my sql
statements as well. If anyone can make sense out of this, please help me
retain my sanity :>) I am using a pair of nested queries.

Query 1

SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.Name, Table1.Prefix,
Table1.SSN, Table1.[UCA Code], Table1.Surgeon, Table1.Procedure, Table1.
TimeOutOfOR, Int((Table1_Dup!TimeInOR-Table1!TimeOutOfOR)*24*60) AS Elapsed,
Table1_Dup.TimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup ON Table1.Date = Table1_Dup.Date
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[enter OR#]) AND ((Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]
) AND ((Table1_Dup.[OR#])=[Table1]![OR#]))
ORDER BY Table1.Date, Table1_Dup.[OR#];

Query 2

SELECT BetweenTimesQuery1.ID AS Expr1, BetweenTimesQuery1.Date,
BetweenTimesQuery1.[OR#], BetweenTimesQuery1.TimeOutOfOR, Min
(BetweenTimesQuery1.Elapsed) AS [ElapsedTimeBetween TimeoutORandNextTimeInOR]
FROM BetweenTimesQuery1
GROUP BY BetweenTimesQuery1.ID, BetweenTimesQuery1.Date, BetweenTimesQuery1.
[OR#], BetweenTimesQuery1.TimeOutOfOR
HAVING (((BetweenTimesQuery1.[OR#])=[enter OR#]))
ORDER BY BetweenTimesQuery1.TimeOutOfOR;

Any help would be greatly appreciated.
VR,
Red

 

[John Vinson]

Hi everyone,
I have been working on a query for the past couple of weeks and thanks to
everyone who has given me suggestions on how to work it. The problem is that
my elapsed time is sometimes off by 1 minute and I cant figure out why and is
not consistent. Let me show you my the result;

Umm?

Why not use the built-in DateDiff() functions rather than subtracting
Date/Time values? YOu can get the time elapsed in seconds or in
minutes; in your case I'd suggest using seconds, since DateDiff counts
boundaries, rather than rounding: e.g. DateDiff("n", #10:59:59#,
#11:00:00#) returns 1 minute though only one second has passed.

Try

SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.[Name],
Table1.Prefix,Table1.SSN, Table1.[UCA Code], Table1.Surgeon,
Table1.Procedure, Table1.TimeOutOfOR,
DateDiff("s",Table1_Dup!TimeInOR,Table1!TimeOutOfOR)/60 AS Elapsed,
Table1_Dup.TimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup
ON Table1.[OR#] = TableDup.[OR#]
ON Table1.[Date] = Table1_Dup.[Date]
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:])
AND ((Table1.[OR#])=[enter OR#]) AND
((Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR])
ORDER BY Table1.Date, Table1_Dup.[OR#];

Note: Date is a BAD choice of fieldnames, as it's a reserved word for
the today's-date function Date(); Access *will* get confused. The same
applies to Name - Table1 *has* a Name property, it's "Table1". I also
suggest using the ON clause of the join to link the two instances of
the table rather than the WHERE clause.

John W. Vinson[MVP]

 

[red skelton via AccessMonster.com]

Hi Sam,
by real time field do you mean Date/Time? If so, they are in the proper
format. If you mean something else, please explain. As far as Red Skelton,
he actually died a couple of years ago. I still see his TV program on
television once in a while is is good. Yes, it does make me feel just a bit
old :<(
Thanks,
Red

I think the problem is that your time fields are pure numeric instead of
actual time fields, unless I'm way off base. I think if you change the time
in and out fields to real time fields, and calculate the elapsed time by
adding or subtracting time, your problem will go away.

Sam

ps red skelton, eh? Is he still on TV? He was on when I was a kid, and I'm
almost 57 now. I guess we're both showing our age!

Hi everyone,
I have been working on a query for the past couple of weeks and thanks to

[quoted text clipped - 44 lines]

VR,
Red

 

[red skelton via AccessMonster.com]

Thanks for the reply,
I tried the DateDiff function and come out with exactly the same numbers as I
did previously using the subtraction method. to me, it doesn't make any
sense at all but I'm a Beginner at this. As far naming the tables, I totally
underatand. Unfortunatly I inherited this database and it has grown into a
monster. To go into the program to change it now would create too many
problems.

Thanks for your help,
Red

Hi everyone,
I have been working on a query for the past couple of weeks and thanks to
everyone who has given me suggestions on how to work it. The problem is that
my elapsed time is sometimes off by 1 minute and I cant figure out why and is
not consistent. Let me show you my the result;

Umm?

Why not use the built-in DateDiff() functions rather than subtracting
Date/Time values? YOu can get the time elapsed in seconds or in
minutes; in your case I'd suggest using seconds, since DateDiff counts
boundaries, rather than rounding: e.g. DateDiff("n", #10:59:59#,
#11:00:00#) returns 1 minute though only one second has passed.

Try

SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.[Name],
Table1.Prefix,Table1.SSN, Table1.[UCA Code], Table1.Surgeon,
Table1.Procedure, Table1.TimeOutOfOR,
DateDiff("s",Table1_Dup!TimeInOR,Table1!TimeOutOfOR)/60 AS Elapsed,
Table1_Dup.TimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup
ON Table1.[OR#] = TableDup.[OR#]
ON Table1.[Date] = Table1_Dup.[Date]
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:])
AND ((Table1.[OR#])=[enter OR#]) AND
((Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR])
ORDER BY Table1.Date, Table1_Dup.[OR#];

Note: Date is a BAD choice of fieldnames, as it's a reserved word for
the today's-date function Date(); Access *will* get confused. The same
applies to Name - Table1 *has* a Name property, it's "Table1". I also
suggest using the ON clause of the join to link the two instances of
the table rather than the WHERE clause.

John W. Vinson[MVP]

 

[OfficeDev18 via AccessMonster.com]

In that case, Red, try putting your times into variables, doing the math, and
extracting time again. For example:

TimeOut = 8:00; TimeIn = 8:24 (sample times and field names)

Dim TICalc as Long, TOCalc as Long,TDiff as Date

TOCalc = Hour(TimeOut)*60+Minute(TimeOut)
TICalc = Hour(TimeIn)*60+Minute(TimeIn)
TDiff = (TICalc-TOCalc)/(24*60) 'the reason for this is to tell Access
you're dealing with minutes and not days.
Since TDiff is a Date field, it should display "00:24", which is what I got
when I ran this snippet in my Immediate Window.

I believe this is what you wanted to display, right?

Sam

Thanks for the reply,
I tried the DateDiff function and come out with exactly the same numbers as I
did previously using the subtraction method. to me, it doesn't make any
sense at all but I'm a Beginner at this. As far naming the tables, I totally
underatand. Unfortunatly I inherited this database and it has grown into a
monster. To go into the program to change it now would create too many
problems.

Thanks for your help,
Red
[quoted text clipped - 32 lines]

John W. Vinson[MVP]

 

[red skelton via AccessMonster.com]

Thanks Sam,
I tried to use the sql below but I get the following error message:

The SELECT Statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.Name, Table1.Prefix,
Table1.SSN, Table1.[UCA Code], Table1.Surgeon, Table1.Procedure, Table1.
TimeOutOfOR,
Dim TimeInOR as long, TimeOutOfOR as long, TDiff as Date
TimeOutOfORCalc = Hour(TimeOutOfOR)*60+Minute(TimeOutOfOR)
TimeInOR = Hour(TimeInOR)*60+Minute(TimeInOR)
TDiff = (Table1_Dup!TimeInOR-TimeOutOfOR)/24*60) AS Elapsed, Table1_Dup.
TimeTimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup ON Table1.Date = Table1_Dup.Date
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[enter OR#]) AND ((Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]
) AND ((Table1_Dup.[OR#])=[Table1]![OR#]))
ORDER BY Table1.Date, Table1_Dup.[OR#];


I include my sql for you. As you probably can tell, I'm fairly new at this
and any help is greatly appreciated.
Many Thanks,
Red

In that case, Red, try putting your times into variables, doing the math, and
extracting time again. For example:

TimeOut = 8:00; TimeIn = 8:24 (sample times and field names)

Dim TICalc as Long, TOCalc as Long,TDiff as Date

TOCalc = Hour(TimeOut)*60+Minute(TimeOut)
TICalc = Hour(TimeIn)*60+Minute(TimeIn)
TDiff = (TICalc-TOCalc)/(24*60) 'the reason for this is to tell Access
you're dealing with minutes and not days.
Since TDiff is a Date field, it should display "00:24", which is what I got
when I ran this snippet in my Immediate Window.

I believe this is what you wanted to display, right?

Sam

Thanks for the reply,
I tried the DateDiff function and come out with exactly the same numbers as I

[quoted text clipped - 12 lines]

 

[OfficeDev18 via AccessMonster.com]

Uh, Red, you got mixed up between SQL and VBA. My code was in VBA; the two
language syntaxes are not the same.

What I was suggesting was that you open a Recordset in VBA (using the SQL
statement as the Recordset arguement), extracting the times into VBA and
doing the math, and display the results using the information that is now in
the program's variable.

In other words, you might have a VBA subroutine like this:

Private Sub DisplayTimes()

Dim Rst as DAO.Recordset, TICalc as Long, TOCalc as Long,TDiff as Date

Set Rst=CurrentDb.OpenRecordset("SELECT....",dbOpenDynaset) 'just the
basic fields for now
With Rst
.MoveFirst
TOCalc = Hour(!TimeOut)*60+Minute(!TimeOut)
TICalc = Hour(!TimeIn)*60+Minute(!TimeIn)
TDiff = (TICalc-TOCalc)/(24*60) 'the reason for this is to tell
Access you're dealing with minutes and not days.
MsgBox "The Time Elapsed is " & Format(TDiff,"Short Time")
.Close
End With
End Sub

By the way, this assumes you're using DAO, not ADO. If you run the code as is,
and you get an error message saying something like "Library not available",
or something like that, you're using ADO. In that case, simply remove the
"DAO" from the Dim statement in the first line of the program. Also, I think
you have to change the line that says ".MoveFirst" to ".Move 1". Not sure
about that.

Good luck,

Sam

Thanks Sam,
I tried to use the sql below but I get the following error message:

The SELECT Statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect.

SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.Name, Table1.Prefix,
Table1.SSN, Table1.[UCA Code], Table1.Surgeon, Table1.Procedure, Table1.
TimeOutOfOR,
Dim TimeInOR as long, TimeOutOfOR as long, TDiff as Date
TimeOutOfORCalc = Hour(TimeOutOfOR)*60+Minute(TimeOutOfOR)
TimeInOR = Hour(TimeInOR)*60+Minute(TimeInOR)
TDiff = (Table1_Dup!TimeInOR-TimeOutOfOR)/24*60) AS Elapsed, Table1_Dup.
TimeTimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup ON Table1.Date = Table1_Dup.Date
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[enter OR#]) AND ((Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]
) AND ((Table1_Dup.[OR#])=[Table1]![OR#]))
ORDER BY Table1.Date, Table1_Dup.[OR#];

I include my sql for you. As you probably can tell, I'm fairly new at this
and any help is greatly appreciated.
Many Thanks,
Red

In that case, Red, try putting your times into variables, doing the math, and
extracting time again. For example:

[quoted text clipped - 19 lines]