Automation error in report

[LAS]

When I run my report I get an "automation error" each time it tries to display this in the detail section. I've compressed and repaired the report and have removed and replaced the detail item that is failing. Google suggests much more esoteric problems than I think I'm dealing with. It displays the detail OK if I don't put it in the function.

It fails on this detail item.

=fncMinutesFormatted([ElapsedTime])

But it displays this in the group totals just fine
=fncMinutesFormatted(Sum([ElapsedTime]))

Here is the definition of ElapsedTime in the query that is fhe report's record source

ElapsedTime: fncElapsedTime([incident_time],[Incident_Return_Time])

And here are the two functions mentioned above.

Public Function fncElapsedTime(dtStart As Date, dtEnd As Date) As Integer

On Error GoTo Err_ElapsedTime

fncElapsedTime = 0

If IsNull(dtStart) Or IsNull(dtEnd) Then Exit Function

gv_variant = DateDiff("n", dtStart, dtEnd)

fncElapsedTime = gv_variant

gs_Debug = "stop"
Exit_ElapsedTime:
Exit Function

Err_ElapsedTime:
is_temp = "fncElapsedTime: " & Err.Description
MsgBox is_temp
Resume Exit_ElapsedTime

End Function


Public Function fncMinutesFormatted(av_Minutes As Variant) As String

On Error GoTo Err_Minutes

fncMinutesFormatted = "0"

If IsNull(av_Minutes) Then Exit Function

gv_variant = av_Minutes \ 60 'I put all these assignments in in an attempt to debug.
gv_variant = gv_variant & ":"
gv_variant = Format(av_Minutes Mod 60, "00")

gv_variant = av_Minutes \ 60 & ":" & Format(av_Minutes Mod 60, "00")


fncMinutesFormatted = gv_variant

gs_Debug = "stop"
Exit_Minutes:
Exit Function

Err_Minutes:
is_temp = "fncMinutesFormatted: " & Err.Description
MsgBox is_temp
Resume Exit_Minutes

End Function

 

[David W. Fenton]

It fails on this detail item.

=fncMinutesFormatted([ElapsedTime])

But it displays this in the group totals just fine
=fncMinutesFormatted(Sum([ElapsedTime]))

Here is the definition of ElapsedTime in the query that is fhe
report's record source

ElapsedTime:
fncElapsedTime([incident_time],[Incident_Return_Time])

Aliases don't always work reliably. I'd replace [ElapsedTime] with
the expression that ElapsedTime is an alias for.

 

[LAS]

Sorry, I don't understand the difference between "alias" and "expression."
Could you give me an example of what I should do?

It fails on this detail item.

=fncMinutesFormatted([ElapsedTime])

But it displays this in the group totals just fine
=fncMinutesFormatted(Sum([ElapsedTime]))

Here is the definition of ElapsedTime in the query that is fhe
report's record source

ElapsedTime:
fncElapsedTime([incident_time],[Incident_Return_Time])

Aliases don't always work reliably. I'd replace [ElapsedTime] with
the expression that ElapsedTime is an alias for.

 

[John Spencer]

Your function - fncElapsedTime - is going to error if any record has null for
[incident_time] or [Incident_Return_Time].

The function declaration requires that you pass it two dates (nulls not
allowed). The error will occur before you can do any error-trapping.

Also the function can only return numbers between -32K and +32K since you have
told it to return and integer value. You might want to declare the returned
value as Long or even variant if you want to return NULL.

Public Function fncElapsedTime(dtStart As Date, dtEnd As Date) As Integer

You could try changing that to
Public Function fncElapsedTime(dtStart, dtEnd) As Integer
(dtStart and dtEnd will default to Variant types which will no longer cause an
error to be returned when you attempt to use the function.

You could also test the two values with the IsDate function instead of testing
for null.
If IsDate(dtStart) and IsDate(dtEnd) Then
fncElapsedTime = DateDiff("n",dtStart,dtEnd)
ELSE
fncElapsedTime = 0 'Must return an integer number.

'or if desired fncElapsedTime = null
'requires you change return value declaration to variant instead of single.
End If

Of course you could just call the DateDiff function directly. It does handle
nulls with no problem

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

When I run my report I get an "automation error" each time it tries to display this in the detail section. I've compressed and repaired the report and have removed and replaced the detail item that is failing. Google suggests much more esoteric problems than I think I'm dealing with. It displays the detail OK if I don't put it in the function.

It fails on this detail item.

=fncMinutesFormatted([ElapsedTime])

But it displays this in the group totals just fine
=fncMinutesFormatted(Sum([ElapsedTime]))

Here is the definition of ElapsedTime in the query that is fhe report's record source

ElapsedTime: fncElapsedTime([incident_time],[Incident_Return_Time])

And here are the two functions mentioned above.

Public Function fncElapsedTime(dtStart As Date, dtEnd As Date) As Integer

On Error GoTo Err_ElapsedTime

fncElapsedTime = 0

If IsNull(dtStart) Or IsNull(dtEnd) Then Exit Function

gv_variant = DateDiff("n", dtStart, dtEnd)

fncElapsedTime = gv_variant

gs_Debug = "stop"
Exit_ElapsedTime:
Exit Function

Err_ElapsedTime:
is_temp = "fncElapsedTime: " & Err.Description
MsgBox is_temp
Resume Exit_ElapsedTime

End Function


Public Function fncMinutesFormatted(av_Minutes As Variant) As String

On Error GoTo Err_Minutes

fncMinutesFormatted = "0"

If IsNull(av_Minutes) Then Exit Function

gv_variant = av_Minutes \ 60 'I put all these assignments in in an attempt to debug.
gv_variant = gv_variant & ":"
gv_variant = Format(av_Minutes Mod 60, "00")

gv_variant = av_Minutes \ 60 & ":" & Format(av_Minutes Mod 60, "00")


fncMinutesFormatted = gv_variant

gs_Debug = "stop"
Exit_Minutes:
Exit Function

Err_Minutes:
is_temp = "fncMinutesFormatted: " & Err.Description
MsgBox is_temp
Resume Exit_Minutes

End Function

 

[David W. Fenton]

I don't understand the difference between "alias" and
"expression." Could you give me an example of what I should do?

In your recordsource you had this aliased field:

ElapsedTime:
fncElapsedTime([incident_time],[Incident_Return_Time])

"ElapsedTime" is the alias for the expression
"fncElapsedTime([incident_time],[Incident_Return_Time])". What I'm
suggesting is using the full expression each time, instead of
depending on correct resolution of the alias.