VBA number format syntax

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

Guest

I have a function which performs a calculation which is in turn called by a
query. Then I have a form with a bound control to display the result. The
problem is I cannot get it to format properly. I've set all the properties
for the text box, but it still shows an arbitrary number of decimel points
and even scientific notation. Can I format the result in the function? Here's
the code.
Public Function Calc_Int(Net, Gross)


If Net = Gross Then
Calc_Int = 1
Else: Calc_Int = Net / (Nz(Gross, 0))
End If

End Function
Thanks
 
The problem is most likely that Access does not understand that the field is
intended to be numeric.

To solve it, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

(Additionally, the function has no return type, and could cause a division
by zero error.)
 
Just as an aside, you definitely definitely don't want Nz to return 0 if the
value of Gross is Null, since that will raise a "Divide by zero" error.
 
Back
Top