Adding time calculated fields in report

[TheCleaner]

New road block. I have the report calculating the =GetElapsedTime Function. After the following the advice of many, this calculated value is NOT stored anywere (only displayed on the report). I've adjusted the function so that it displays only the values I need (omitting the "seconds", leaving "12 Hours 22 Minutes..or whatever). Now, in the footer of the report I would like for those values to be totaled. For example "4563 Hours 53 Minutes".
I've tried this;
Function GetTimeCardTotal ()
Dim db As Database, rs As Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = dbengine.workspaces(0).databases(0)
Set rs = db.OpenRecordset("TimeLog by pilot&quot
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![ElapsedTime]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetTimeCardTotal = totalhours &" hours and " &minutes &" minutes"

End Function

.. only to get a "too few perameters error". Obviously the report is based on a query so that I can filter what I want on the report. ANY help would be great..I'm almost Finished!!

 

[kathryn]

OK, where do you get the error?

Is the GetElapsedTime calculated for each detail line? If so, could you use a global variable to sum the values, using the Print Event of the Detail Section? I just did a test and it works.

Let me know if this would work for you. Kathryn

 

[TheCleaner]

when opening the form I get a "runtime error 3061 too few perameters. expected 1". The GetElapsedTime function is the control source for my [ElapsedTime], it is in the details, and it does the math/function for each record. It't in the footer that I want the totals of the [ElapsedTime]'s. I'm sorry but I have no idea how to do what you spoke of. I'll need A LOT of guidance. Thank you in advance. J

 

[kathryn]

OK, it looks like you have two issues:

The "too few parameters" error, which may be due to the query on which the report is based. Can you post the record source of the report?

To help you with the rest, I need to know what datatype your GetElapsedTime is returning. Can you post that code?

Thanks Kathryn

 

[MichaelRed]

Just a guess, but if the rs ("TimeLog by pilot&quot is a parameter query (e.g. REQUIRES an input), then you cannot instantiate it as a recordset. It would need to declared and instantiated as a query def - witht the appropiate parameter - and then a recordset generated from the querydef object.

This probably sounds mostly like greek, but at least you should be able to determine if the rs is a PARAMETER query.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[TheCleaner]

This is the code for the ElapsedTime Calc.

Function GetElapsedTime(interval)
Dim totalhours As Long, totalminutes As Long, totalseconds As Long
Dim days As Long, hours As Long, minutes As Long, Seconds As Long

days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60

GetElapsedTime = hours & " Hours " & minutes & " Minutes "


End Function

Yes you were speaking in Greek & yes it is based on a query that uses the pilot's name as a perameter. Data type is date/time, general. Thanks again.

 

[MichaelRed]

Yes, but 'interval' in passed to this code. So the question is not really about THIS code, but the part where a value 'interval' is assigned to 'interval'.

Here, you need to set the pilot's name as the parameter for the query. Since n=my preceeding post is in 'Greek', it may be difficult for me to coach you through the process from this perspective.

To use the approach I outlined (the 'Greek', you would need to lookup QueryDef and Parameter in help. It might be somewhat confusing, especially the parameter, as the term is used in more than one context. Aslo add parameter query to the list of what to lookup.

You could also look up the "DSum" function. It could be easier for you to understand.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[raskew]

Function GetElapsedTime() is accepting number of days as the interval, as evidenced by: days = Int(CSng(interval)). Is is your intent to just take a total number of days and then express that in terms of hours and minutes? If not, the code needs to be modified because, as written, you'll never get anything like 12 hours and 22 minutes.

 

[raskew]

Oops--let me modify that, I spoke too soon. What I said applies if you are entering whole numbers, but would not necessarily apply with decimals, e.g., an interval of 18.5 would result in
18 Days 12 Hours 0 Minutes.