MS Access 2003 Formatting Hours Field

[markcupito]

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]

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.

 

[Access Developer]

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.