Help with This Expression

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

Guest

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
 
Levans digital said:
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
Click to expand...

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)
Click to expand...

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 said:
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
Click to expand...

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)
Click to expand...

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)
 
Hey,
You are a Genious it works beautifully.

Eldon

Dirk Goldgar said:
Levans digital said:
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
Click to expand...

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)
Click to expand...

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)
Click to expand...
 
Hey,
You are a Genious it works beautifully.

Eldon

Dirk Goldgar said:
Levans digital said:
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
Click to expand...

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)
Click to expand...

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)
Click to expand...
 
Back
Top