MS Access 2003 Formatting Hours Field

Discussion in 'Access Forms Coding' started by markcupito@gmail.com, 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_Handler:
    Exit Sub
    Err_Handler:
    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
    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
    time.

    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.

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    <> wrote in message
    news:...
    > 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_Handler:
    > Exit Sub
    > Err_Handler:
    > 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.
     
    Access Developer, Aug 12, 2013
    #2
    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.
    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    <> wrote in message
    news:...
    > 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_Handler:
    > Exit Sub
    > Err_Handler:
    > 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.
     
    Access Developer, Aug 13, 2013
    #3
    1. Advertisements

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.
Similar Threads
  1. Jerome

    Adding hours to a time field?

    Jerome, Oct 15, 2003, in forum: Access Forms Coding
    Replies:
    1
    Views:
    97
    Allen Browne
    Oct 15, 2003
  2. Fr@nk
    Replies:
    2
    Views:
    100
    Fr@nk
    Jan 10, 2004
  3. quixote

    Access field to Word field.

    quixote, Sep 16, 2004, in forum: Access Forms Coding
    Replies:
    0
    Views:
    91
    quixote
    Sep 16, 2004
  4. JBalkan
    Replies:
    1
    Views:
    152
    Graham Mandeno
    Mar 17, 2005
  5. Tony

    Remove Hours, Minutes and Seconds from Date Field

    Tony, Apr 7, 2005, in forum: Access Forms Coding
    Replies:
    1
    Views:
    249
    Ken Snell [MVP]
    Apr 7, 2005
  6. Frank

    Time in terms of hours expression in text field.

    Frank, Jul 12, 2005, in forum: Access Forms Coding
    Replies:
    6
    Views:
    94
    Frank
    Jul 14, 2005
  7. Replies:
    3
    Views:
    126
    Klatuu
    Jun 6, 2006
  8. Replies:
    2
    Views:
    247
Loading...