Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Help with This Expression

Discussion in 'Information Technology' started by Levans digital, Jul 28, 2006.

  1. Levans digital

    Levans digital
    Expand Collapse
    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
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Levans digital" <Levansdigital@discussions.microsoft.com> wrote in
    message news:C18B9683-2921-4439-8576-DCD926E22F6C@microsoft.com
    > 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)
     
  4. Levans digital

    Levans digital
    Expand Collapse
    Guest

    Hey,
    You are a Genious it works beautifully.

    Eldon

    "Dirk Goldgar" wrote:

    > "Levans digital" <Levansdigital@discussions.microsoft.com> wrote in
    > message news:C18B9683-2921-4439-8576-DCD926E22F6C@microsoft.com
    > > 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)
    >
    >
    >
     

Share This Page