How come I'm getting a #Error message?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have included a module in a report that rounds a calculated number off to the nearest 10. It works well where data exists to be calculated, however, if no data is present (data to be entered at a future date) I get a #Error message printed on the report. Is there any way to prevent the module from attempting to calculate this field if no data is present?
 
Pretty hard to comment without knowing what the module's doing!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Martin Holubar said:
I have included a module in a report that rounds a calculated number off
Click to expand...
to the nearest 10. It works well where data exists to be calculated,
however, if no data is present (data to be entered at a future date) I get a
#Error message printed on the report. Is there any way to prevent the
module from attempting to calculate this field if no data is present?
 
Here is the module:



Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double
Dim dblTemp As Double
Dim lngTemp As Long
dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)
If dblNumber = 0 Then
RoundToNearest = dblNumber
Else
If lngTemp = dblTemp Then
RoundToNearest = dblNumber
Else
If IsMissing(varUp) Then
' round down
dblTemp = lngTemp
Else
' round up
dblTemp = lngTemp + 1
End If
RoundToNearest = dblTemp * varRoundAmount
End If
End If
End Function
 
You will either have to force the nulls to some value, use an IIF to avoid the
function or change the function.

Force to some value

SELECT RoundToNearest(CDbl(NZ(SomeField,0)),2) as RoundedField,
....

Use IIF (Perhaps the most efficient in SQL)

SELECT IFF(SomeField is Null,Null,RoundToNearest(SomeField),2),
....

Change the function declaration. If what you pass to dblNumber (as number) is a
null, you will get an error that you can't trap within the function's code.
Also, you will either have to pass back some default value (0?) if you choose to
define the return value as Double.


Function RoundToNearest (vNumber as Variant,dblRoundAmount as Double, _
Optional varUp as Variant) as Variant

If IsNumeric(vNumber) = false then
RoundToNearest = Null
Else
'Run your current code
End if

Exit Function.
 
Thanks, that did it!

John Spencer (MVP) said:
You will either have to force the nulls to some value, use an IIF to avoid the
function or change the function.

Force to some value

SELECT RoundToNearest(CDbl(NZ(SomeField,0)),2) as RoundedField,
....

Use IIF (Perhaps the most efficient in SQL)

SELECT IFF(SomeField is Null,Null,RoundToNearest(SomeField),2),
....

Change the function declaration. If what you pass to dblNumber (as number) is a
null, you will get an error that you can't trap within the function's code.
Also, you will either have to pass back some default value (0?) if you choose to
define the return value as Double.


Function RoundToNearest (vNumber as Variant,dblRoundAmount as Double, _
Optional varUp as Variant) as Variant

If IsNumeric(vNumber) = false then
RoundToNearest = Null
Else
'Run your current code
End if

Exit Function.
Click to expand...
 
Back
Top