Validation on a form

[RickR]

Hello All,

I have a database where the user enters the hours an employee had worked that week. The number is later used to figure if an employee gets an overtime commission and if so how much. ( a bunch of Chinese arithmetic the IRS dreamed up). Commission changes every quarter hour, so at 40.25 hours its .003 percent, at 40.50 hours its .006 percent…so on and so forth.
The user knows that they are supposed to put the hours in quarter increments, however I want to be sure because the lookup table will not give a correct commission if they are not.
I have tried to validate the number put in by putting in some code on the BeforeUpdate looking like this:

Dim Hours As Integer

If Hours < 40.24 Then Hours = 40
If Hours < 40.49 Then Hours = 40.25
~snip~
If Hours < 60.74 Then Hours = 60.5
If Hours < 60.99 Then Hours = 60.75

I don’t get any error on this, in fact It doesn’t seem to do anything. What ever I enter stays put and is not changed.

Question 1, Am I approaching this correctly, using the right type of validation and at the right place.

Question 2, How in the world do I make it work!


Many thanks for any help.

Rick

 

[David R]

How about:

Private Sub Hours_BeforeUpdate(Cancel As Integer)
   If(Int(Me.Hours.Value * 4) <> Me.Hours.Value * 4) Then Cancel = True
End Sub

 

[charityg]

If you want to automatically change the value without forcing the user to re-enter the hours, use this:

Private Sub hours_AfterUpdate()
Dim response As Variant
Dim whole
Dim dec
    If (hours * 100) Mod 25 > 0 Then
        If InStr(1, hours, ".") > 0 Then
            whole = Left(hours, Len(hours) - InStr(1, hours, ".") + 1)
            dec = Right(hours, InStr(1, hours, "."))
            whole = Val(whole)
            dec = Val(dec)

        End If
        Select Case dec
            Case Is < 0.25
                hours = whole
            Case Is > 0.25
                If dec < 0.5 Then
                    hours = whole & ".25"
                ElseIf dec < 0.75 Then
                    hours = whole & ".5"
                ElseIf dec <= 0.999999 Then
                    hours = whole & ".75"
                End If
            End Select
    End If
End Sub

 

[RickR]

Thank you both so much for your help, I have that portion up and running now.

Rick

 

[teiben]

I pasted this in and it doesn't work, it errors out ~ syntex

If (hours * 100) Mod 25 > 0 Then
I think it;s the " ; " did it copy wrong?