Limiting decimals 1

[dcurtis]

I have a field (control) for hours worked and need to be able to limit it to 1/4, 1/2 and 3/4 hour increments. For example 8.0, 8.25 (for 15 minutes, which some users can't figure out and still put .15), 8.5 or 8.75 are accepted, but 8.15 is not.....I thought about an If statement but 7.25, 7.5 etc is also acceptable. How to I set validation for the decimal portion of the number only, or is it not possible?

Thanks in advance for any and all help.

----
Access 2002 on a mixed Windows OS network.

 

[randy700]

How about something like this....

Select Case Right(HrsWorked * 100, 2)
Case 0, 25, 50, 75:
'The input is acceptable.
Case Else:
Msgbox "Valid decimals are .25, .50, .75, .00 ONLY"
End Select



Randy

 

[BobJacksonNCI]

dcurtis,

I've marked your thread out of curiosity...

There MUST be an easy way for this as it is likely a common requirement.

While we're waiting for a better answer, you could create a Before Update event for the control. In that event, you could put in: (example field being hrswrkd)

If ((hrswrkd * 100) - (Int(hrswrkd) * 100)) not in (00, 25, 50, 100) then
msgbox "You must enter hours worked in quarter hour increments - .00, .25, .50 or .75", vbokonly, "INCORRECT HOURS ENTERED"
docmd.cancelevent
end if

The above comes with my UNCONDITIONAL 30/30 GUARANTEE!

(30 Seconds or 30 Feet, whichever comes first)



HTH,
Bob

 

[BobJacksonNCI]

randy700,

I was typing while you were typing...

You solution is more concise - I like that.

What happens if the user doesn't enter two decimals?

Bob

 

[randy700]

The number of decimals entered by the user won't matter because of the multiplication by 100 -- 8.5 * 100 = 850, which is acceptable. It shouldn't be too difficult to take this a step further and make the correction instead of just throwing up a message box but I haven't worked that out yet.

Randy

 

[dcurtis]

Randy, exactly what I was looking for. THANKS!!

 

[dcurtis]

OK, that worked perfectly, but led to another problem. Is there a way to put that in a public function and use it to verify multiple fields? I have 6 categories (reg. OT, sick.....) etc times 7 days of the week. My DB will bloat if I add the select case statement to each field.

I defined a public variable (myHours), then in the before update of the hours field (SundayRegular, MondayRegular etc) used:

myHours = Right(Me.SundayRegular * 100, 2)
myCheck

calling the public function (myCheck) that performs the check (above, from Randy700, having changed it HrsWorked to myHours).

It allows any value in. If that was confusing let me know and I will try to rewrite it more clearly.

THANKS AGAIN FOR THE HELP.

 

[randy700]

How about this.... (not tested)

Private Sub myHours_BeforeUpdate (Cancel as Integer)
if myCheck(Me.myHours) then
Cancel = false
else
Cancel = true
End If
End Sub

Public Function myCheck(x As Double) As Boolean
Select Case Right(x * 100, 2)
Case 0, 25, 50, 75:
myCheck = True
Case Else:
myCheck = False
msgBox "Invalid entry."
End Select
End Function

Randy

 

[dcurtis]

Thanks again - it work's like a charm!