Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the following bit of code,which works just fine, however the last update returns a value rounded to the nearest number down and I want it rounded to the nearest number up - is there a way I can do this? For example, if the inital value is 85, its returning a value of 42 when I want 43. Thanks for your help.


    Sub AddTest()
    AddData "SubProcesses", 10

    End Sub

    'ADDS TOTALS FROM SubProcesses TABLE TO LINE 10 OF TBLA
    Sub AddData(strSource As String, lngLineID As Long)
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim i As Long
    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset(strSource, dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("SELECT * FROM TblA WHERE LineID=" & _
    lngLineID, dbOpenDynaset)
    ' Optional: set all fields to 0 except the first two

    rstOut.Edit
    For i = 3 To rstOut.Fields.Count - 1
    rstOut.Fields(i) = 0
    Next i
    rstOut.Update

    ' Loop through data records
    Do While Not rstIn.EOF

    ' Update appropriate field and total
    rstOut.Edit
    rstOut.Fields(rstIn!BU) = rstOut.Fields(rstIn!BU) + 1
    rstOut!Total = rstOut!Total + 1
    rstOut.Update

    rstIn.MoveNext
    Loop

    'Divide totals by half
    rstOut.Edit
    rstOut!Total = (rstOut!Total) * 0.5
    rstOut!GCC = (rstOut!GCC) * 0.5
    rstOut!PBS = (rstOut!PBS) * 0.5
    rstOut!CR = (rstOut!CR) * 0.5
    rstOut!BOPS = (rstOut!BOPS) * 0.5
    rstOut!GOFIN = (rstOut!GOFIN) * 0.5
    rstOut.Update


    ExitHandler:
    On Error Resume Next
    rstOut.Close
    Set rstOut = Nothing
    rstIn.Close
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Access Unlike Excel only has a Round Function, NOT a RoundDown or RoundUp function
    so I think you are going to have to use a Custom Function in a Module and then call it from your routine

    I have put one below
    I haven't bothered to error trap it for NULL inputs but you could
    It also rounds to nearest 10, 100 etc by using - places

    Code:
    Function RoundDown(dblVal As Double, intDP As Integer) As Double
    
    Dim lngVal As Long, dblRound As Double
    
    lngVal = Int(dblVal * 10 ^ intDP)
    
    dblRound = lngVal / (10 ^ intDP)
    
    RoundDown = dblRound
    
    End Function
    You would then have to call this as part of the calculation in your code
    No idea what accuracy you are after, but for example

    Code:
    rstOut!Total = RoundDown((rstOut!Total) * 0.5,1)


    Would round Down the answer to 1 DP


    Andrew

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Just to clarify - if you have a even number and the result is exactly half of the original value, do you want to round that up, or leave it at exactly half? Also are you using integer or floating point field types?
    Wendell

  4. #4
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi both, thanks for your responses - yes I am using Long Integer and I want to round it up. I have got a fairly simple work around by multiplying by 0.5001 now which gives the right results, but I'm worried its not technically correct/best. For these tables its unlikely to return a value of greater than a couple of thousand so I didn't think it would be too important, but I'd prefer to know the right way to do it for the future.

    Best.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by DazedandConfused View Post
    Hi both, thanks for your responses - yes I am using Long Integer and I want to round it up. I have got a fairly simple work around by multiplying by 0.5001 now which gives the right results, but I'm worried its not technically correct/best. For these tables its unlikely to return a value of greater than a couple of thousand so I didn't think it would be too important, but I'd prefer to know the right way to do it for the future.

    Best.
    Does not the RoundDown function used for example as below do the same job

    rstOut!Total = RoundDown(rstOut!Total,0)


    Andrew

  6. #6
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi as a beginner not sure of these functions - will have a check to see if there's a roundup function, which is the one I'd be needing. Thanks for your help.

  7. #7
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Access 2003 and cannot find a RoundDown function (or a RoundUp function which is what I'd be needing) in the help files - is it only relevant to later versions? Best

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by DazedandConfused View Post
    Hi as a beginner not sure of these functions - will have a check to see if there's a roundup function, which is the one I'd be needing. Thanks for your help.
    OOPS, sorry, misread the original question and thought you were after a RoundDown function

    Watch this space!
    Andrew

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    IF all you want to do is to RoundUp to the Nearest Whole Number then you could use this function

    Code:
    
    Function RoundUp(dblVal As Double) As Long
    
    Dim dblRound As Double
    
    If (dblVal - Int(dblVal)) <> 0 Then
        dblRound = Round(dblVal + 0.5,0)
    Else
        dblRound = dblVal
    End If
    
    RoundUp = dblRound
    
    End Function
    Create a Module and paste the function into it

    Then Just reference it in your routine

    eg


    Code:
    rstOut!Total = RoundUp(rstOut!Total,0)
    (Obviously I meant RoundUp not RoundDown which I had written)

    Or you could just use the Technique in your routine

    e.g.

    rstOut!Total = IIF(rstOut!Total-Int(rstOut!Total)=0,rstOut!Total,Round(rstOut!Tota l+0.5,0))
    Andrew

  10. #10
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excellent - thanks so much Andrew!

    Best.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •