Help with This Expression

[Levans digital]

I am wrapping this expression with the RoundtoNearest function.

I want it to round to the nearest 1 up

example: 34.23 = 35 , 36.55 = 37

=RoundToNearest(((Nz(Sum([LBS]),0)+nz(Sum([CF])*50,0))/2000),1,up)

but it it not working why?

I am using the following code
Optional varUp As Variant) As Double

Dim dblTemp As Double
Dim lngTemp As Long

dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)

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 Function

Thanks

 

[Dirk Goldgar]

I am wrapping this expression with the RoundtoNearest function.

I want it to round to the nearest 1 up

example: 34.23 = 35 , 36.55 = 37

=RoundToNearest(((Nz(Sum([LBS]),0)+nz(Sum([CF])*50,0))/2000),1,up)

but it it not working why?

I am using the following code
Optional varUp As Variant) As Double

Dim dblTemp As Double
Dim lngTemp As Long

dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)

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 Function

Thanks

You left off part of the function header, but it looks as though the
function rounds up if *anything* is passed in the "varUp" parameter.
Your expression:

=RoundToNearest(((Nz(Sum([LBS]),0)+nz(Sum([CF])*50,0))/2000),1,up)

seems to use the word "up". I can't tell if that's supposed to be a
control or variable name. If it's not the name of a control or
variable, I don't think Access is going to recognize it. Try replacing
it with a literal value; maybe like this:

=RoundToNearest(((Nz(Sum([LBS]),0)+nz(Sum([CF])*50,0))/2000),1,1)

 

[Levans digital]

Hey,
You are a Genious it works beautifully.

Eldon

I am wrapping this expression with the RoundtoNearest function.

I want it to round to the nearest 1 up

example: 34.23 = 35 , 36.55 = 37

=RoundToNearest(((Nz(Sum([LBS]),0)+nz(Sum([CF])*50,0))/2000),1,up)

but it it not working why?

I am using the following code
Optional varUp As Variant) As Double

Dim dblTemp As Double
Dim lngTemp As Long

dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)

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 Function

Thanks

You left off part of the function header, but it looks as though the
function rounds up if *anything* is passed in the "varUp" parameter.
Your expression:

=RoundToNearest(((Nz(Sum([LBS]),0)+nz(Sum([CF])*50,0))/2000),1,up)

seems to use the word "up". I can't tell if that's supposed to be a
control or variable name. If it's not the name of a control or
variable, I don't think Access is going to recognize it. Try replacing
it with a literal value; maybe like this:

=RoundToNearest(((Nz(Sum([LBS]),0)+nz(Sum([CF])*50,0))/2000),1,1)


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)