MS Access 2003 Formatting Hours Field

Discussion in 'Access Forms Coding' started by, Aug 12, 2013.

  1. Guest

    Hello all,

    I am new to MS Access 2003, for the most part. (Disclaimer)

    Anyway, I have an "Hours" text box on a form that is programmed as [ServiceHours] .. I need to make the "Hours" do this.. Round(4*[ServiceHours], 0 / 4) to get the hours and to compensate for minutes, only allow quarterly hours. .25, .5, .75 ...

    Here's the code that attempts to compensate for that, but it is buggy, and I don't know how to program the above.

    Private Sub ServiceHours_AfterUpdate()
    On Error GoTo Err_Handler

    Dim number, number2

    number = Int(Me.[ServiceHours])
    number2 = Me.[ServiceHours] - number

    Select Case number2
    Case 0 To 0.125
    Me.[ServiceHours] = Me.[ServiceHours] - number2
    Case 0 To 0.365
    Me.[ServiceHours] = Me.[ServiceHours] - (number2 - 0.25)
    Case 0.366 To 0.625
    Me.[ServiceHours] = Me.[ServiceHours] - (number2 - 0.5)
    Case 0.626 To 0.885
    Me.[ServiceHours] = Me.[ServiceHours] - (number2 - 0.75)
    Case 0.896 To 1
    Me.[ServiceHours] = Me.[ServiceHours] - (number2 - 1)
    ' Round(4 * [ServiceHours], 0) / 4

    End Select

    Exit Sub
    MsgBox Error$
    Resume Exit_Handler
    End Sub

    I'm not familiar on how to code it so I get the results I want.

    Any help + tips would be greatly appreciated.
    , Aug 12, 2013
    1. Advertisements

  2. Your example defines the numbers as variant... did you intend to do so?
    It's not always easy to determine how VBA may handle a variant.

    You didn't state how the data was to be entered... from what you wrote, I
    assume hours and decimal fraction of hours. I'm wondering why you'd ask a
    user to do the mental calculation, and then have to adjust it, on the
    assumption that the user had calculated correctly. Most people think of time
    in terms of hours and minutes... did you consider using two fields, one for
    hours as an integer, the other for minutes, and converting the minutes to
    quarter hours? An alternative would be to expect it to be entered as "time",
    e.g., hh:mm -- there are many built-in functions to manipulate date and

    What are the symptoms of "is buggy"? It's difficult to debug remotely, and
    the less detail in the information provided, the more difficult it is.
    Access Developer, Aug 12, 2013
    1. Advertisements

  3. Another alternative: use two controls for time, either a text box or a combo
    box for hours and a combo box for the quarter hours, limited to list, and
    with the list containing only 0, :15, :30, and :45 if you want to do minutes
    or 0, 1, 2, and 3 if you want to do quarter-hours.
    Access Developer, Aug 13, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.