Calculating an Average Time 1

[ghoff]

I have calculated an average time using a module. The expression looks like this: Expr1: ElapsedTimeString([OrdersWritten],[PatientToFloor]) If I plug this into a query, I can successfully get the elapsed time for individual persons. However, I am looking to get an aggregate average. Yet, when I put in Avg(ElapsedTimeString([OrdersWritten],[PatientToFloor])), I get "data mismatch in criteria expression" when running the query. I've also tried skipping this step altogether and trying to plug my expression into the footer of a report based on the query. There, I put in a text box with the expression: Avg([Expr1]), but this also produced an error. Is there some easy step that I'm missing or mistake that I'm making?

Thanks,
HJG

 

[Chad1984]

It's hard to say- it would be helpful if you could post the VBA code in the "ElapsedTimeString" module. I have a few thoughts/questions for you:

Your function is named elapsedtimeSTRING. Does it return a string value or a number? If the former, can strings be averaged?

If that doesn't work, do like I do and mess around a lot with queries. Can you produce a query that shows people and their average times? Then can you produce a different query that selects that value from the first one and sums it, etc?

 

[ghoff]

Your idea of querying a query is fine. These elapsed times are based on a string in a module, by the way.

On a basic level, though, I'm unclear why I'm having trouble averaging values over a period of time. Forget the elapsed times for a moment (since those are date values). If I have 30 counts for the month of June. If I query the counts for June, I get 30 results. If I also put an average expression into the query, it simply gives a column (in the datasheet) with the "average" for each of the 30 days, i.e. the exact same number as in the count column.

Here's the SQL, if anyone can pinpoint what I'm doing wrong. Thanks.

SELECT PFGrandTbl.Date, PFGrandTbl.AdjCenRC, Avg([AdjCenRC]) AS [Avg AdjCenRC]
FROM PFGrandTbl
GROUP BY PFGrandTbl.Date, PFGrandTbl.AdjCenRC
HAVING (((PFGrandTbl.Date) Between [Enter Start Date] And [Enter End Date]));

 

[ghoff]

This is the code for the module used to calculate time differences. Because we have time differences greater than 1 day, I had to find a way to calculate elapsed times using "general dates." Here's the code:

Option Compare Database
Option Explicit

Public Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

Public Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedTimeString(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and an ending
' Date/Time formatted as a string that looks like this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*********************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart

days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))
' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))
' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", minutes & " Minutes"))
str = str & IIf(minutes = "0", "", IIf(seconds <> "0", ", ", " "))
' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", seconds & " Seconds"))
ElapsedTimeString = IIf(str = "", "0", str)
End Function

Public Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
'*********************************************************************
' Function ElapsedDays(dateTimeStart As Date, dateTimeEnd As Date) As String
' Returns the time elapsed in days between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like this:
' "10 days" or "1 day".
'*********************************************************************
Dim interval As Double, days As Variant
If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function
interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
ElapsedDays = IIf(days = 1, days & " Day", days & " Days")
End Function


Is it impossible to do queries using the elpased times that I get from this module?

 

[ck1999]

geoff
try

SELECT PFGrandTbl.Date, Avg([AdjCenRC]) AS [Avg AdjCenRC]
FROM PFGrandTbl
GROUP BY PFGrandTbl.Date
HAVING (((PFGrandTbl.Date) Between [Enter Start Date] And [Enter End Date]));

ck1999

 

[SkipVought]

You are going an EXTREMELY long detour to get, what should be, a simple answer.

Data/Time values are NUMBERS. Time is in units of DAYS -- FRACTIONS of a day, like .25 happens to be 1/4 of a day which is 6 hours. So .25 can be formatted to DISPLAY 6:00.

To get Elapsed time, it is merely t2 - t1.

When you store your Time values, it ought to include a Date part as well (the INTEGER portion of the number).

So let's suppose...
[tt]
Dim t1 As Date, t2 As Date
t1 = #8/27/2007 9:00:00 PM#
t2 = #8/28/2007 1:00:00 AM#
MsgBox Format(t2 - t1, "hh:mm")
[/tt]
Not need for all that string parsing.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[ghoff]

Thanks guys.

Skip, I'm a bit dense on this. I've got, say, two bound boxes on the form, "OrdersWritten" and "PatientToFloor". I've done it so that they are in general date/time format as you've suggested (__/__/____ X:XX:XX AM).

I want an unbound text box that subtracts the first field from the second. I'm trying to deterine how to use the expression you've provided to accomplish this.

Regards,
Geoff

 

[SkipVought]

    me.othertextbox.value = me.PatientToFloor.value - me.OrdersWritten.value

assuming that me.textboxname.value returns a valid Date/Time value.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[ghoff]

When I do what you did above, I get no result in the text box. When I put the following into a query, I get an error result :

DateDiff("Short Time",[TotDtTmOrdWrtn],[TotPtntFlr])

I'm not grasping your message unfortunately. Thanks for trying.

Geoff

 

[SkipVought]

"When I do what you did above, I get no result in the text box"

Where is the CODE that you used???

DateDiff("h",[TotDtTmOrdWrtn],[TotPtntFlr])

You realize that DateDiff does a CONVERSION of the difference and does not return a Time value. it returns either YEARS, MONTHS, DAYS, HOURS, MINUTES, for instance (among others). Not waht you want if you're looking for days, Hours, Minutes & seconds.

Rather ...

Expr: [TotPtntFlr]-[TotDtTmOrdWrtn]

will yield a NUMBER like 1.25, which means 1 1/4 days or 30 hours. Days could be formatted 30:00:00.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]