calling functions from (sub)reports

[Waynest]

I have 2 fields side by side on a subreport, one contains an eight digit date (char) and the other a quantity of minutes (int). For the 1st field, instead of the date I need to show the corresponding day of the week so the textbox on the report contains this

=getdayofweek([PayDate])

And here's the function

Public Function GetDayOfWeek(InDate As Variant) As Variant

Dim DayNo As Integer
Dim DayName As String

DayNo = WeekDay(Date1(InDate))

Select Case DayNo
Case 1
DayName = "Sunday"
Case 2
DayName = "Monday"
Case 3
DayName = "Tuesday"
Case 4
DayName = "Wednesday"
Case 5
DayName = "Thursday"
Case 6
DayName = "Friday"
Case 7
DayName = "Saturday"
End Select
GetDayOfWeek = DayName

End Function

This works fine, the report shows monday, tuesday etc. Now, its neighbour, the minutes field, needs converting to hours and minutes, so the text box contains

=MinsToHrsMins([OffMins])

And here's the function

Public Function MinsToHrsMins(InMins As Variant) As Variant

' Takes 210 and sends back 0330
On Error GoTo Error_Handler
Dim Hrs As Integer
Dim Mins As Integer
Dim HrsStr As String * 2
Dim MinsStr As String * 2

Hrs = Int(InMins / 60)
Mins = InMins - (Hrs * 60)
HrsStr = Format(Hrs, "00&quot
MinsStr = Format(Mins, "00&quot
MinsToHrsMins = HrsStr & ":" & MinsStr

Error_Handler:
End Function

This function never receives the value (Offmins) from the report and consequently always falls thru to the error handler. The function works when called from forms. Can anyone tell me why one function call works and the other doesnt? Its an access project with a sql server database if thats at all relevant.

 

[kathryn]

Some ideas:

When you say the value Offmins is not received, what do you mean. Have you put a breakpoint in the code for the time conversion on the line: Hrs = Int(InMins / 60)? What is the value of InMins? Is it undefined?

Have you put the field OffMins directly on the report to see if it has a value?

Kathryn

 

[Waynest]

Yes, the value of OffMins appears on the report if I dont call the function. But there is no value assigned to InMins in the function if I set a breakpoint. Thats why the function fails on Hrs = Int(InMins / 60).

I'm stumped! The function (I think) must be ok because it works when called from elsewhere. Is there something I am missing with reports/subreports?

 

[kathryn]

Just to clarify, if you put Offmins RIGHT NEXT to the calculated control which uses HrsToMins, does the correct value of OffMins appear. Since we are dealing with subreports, I want to make sure the value is available to the subreport, and not just to the main report.

Kathryn

 

[Waynest]

Yep, side by side, =MinsToHrsMins([Offmins]) is blank and Offmins contains the minute value. Weird, huh? The value wouldnt be available to the main report because that has employee details which is linked to the subreport (pay details) by employee no.

I've just created another simple report to try a few things out and called the function from there with OffMins and that HAS worked. So its something to do with the subreport. I just dont get why calling the GetDayOfWeek function with a field from the same table works ok, but calling MinsToHrsMins with these minute fields doesnt work?!

 

[kathryn]

OK, is OffMins itself a calculated field? I know that that can cause problems.

Other than that, the only suggestion I have is to check for typos, which I am sure you have done.

Sorry not to be of more help!

Kathryn