Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: i have to round a field up in a report

    If the # has anything past the decimal, even .01 i have to round the # up 1. I created a function, but i put this =roundUp([my Field]) as the datasource to the text box and its not working.

    Public Function roundUp(number As Double) As Double
    Dim testValue As String

    testValue = CStr(Nz(number, 0))

    If InStr(testValue, ".") > 0 Then
    roundUp = CInt(number) + 1
    Else
    roundUp = CInt(number)
    End If

    End Function

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This works for me:
    Code:
    Function roundUp(number As Double) As Integer
        If Nz(number, 0) = 0 Then
            roundUp = 0
        Else
            roundUp = CInt(number)
            If number > roundUp Then
                roundUp = roundUp + 1
            End If
        End If
        
    End Function
    A text box in the report with the controlsource: =roundUp(Nz([Number],0))
    displays each number rounded up if there is a decimal.
    Jerry

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It will probably be quicker not use a VB function. There's probably a better way but one possibility:
    Code:
    =Int([my_column])+IIf([my_column]<>Int([my_column]),1,0)
    BTW "Its not working" doesn't help much. Error? Nothing happens? Wrong result is returned? Computer explodes?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup. Running VBA for functions like this does take more processing time than an expression such as pootle flump's.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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