Validation Rule

[sherlocked]

Experts,

I have a form employees use to enter leave time. I would like to set a validation rule on the "hours" field of this form to prevent them from entering leave in increments other than decimal (for example, 90 minutes as 1.5 hours).

I have some folks who will try to enter 90 minutes as 1.30 hours and I would like to prevent this and pop up an error message if they do.

What would the syntax be on this validation rule?

Your help as always is appreciated

 

[plog]

Super confused:

to prevent them from entering leave in increments other than decimal

That sentence means you want to force them to enter decimal values. You do not want them to enter whole numbers.

Furlther, 1.5 and 1.3 are both decimal values. So if people are allowed to put in 1.5 they will also be allowed to enter 1.3.

Perhaps you should change your input to minutes and only allow whole numbers. Or, provide a drop down of accepted values that the user can choose.

 

[arnelgp]

use the Control's BeforeUpdate Event to validate the entry:

Private Sub textBoxName_BeforeUpdate(Cancel As Integer)
If [textBoxName] <> 1.5 Then
MsgBox "Valid leave time value is 1.5"
Cancel =True
End If
End Sub

 

[sherlocked]

could we set this if/then statement up to simply check to see if the field contains a decimal value, and if it does, check to see if it's .25 or .50 or .75 and if not pop up the message?

 

[sherlocked]

I worked up the below code. However, it is not working, even when I enter a value with .30 in the Hours field.

Thoughts?

Private Sub Hours_BeforeUpdate(Cancel As Integer)
If InStr([Hours], ".15" Or ".30" Or ".3" Or ".45", vbTextCompare) Then

        MsgBox "Hours must be entered in 15 minute increments using .25, .5 or .75 values.  Please try again.", vbOKOnly + vbExclamation
        Me.Hours = ""
        Exit Sub
       
End If
End Sub

 

[sherlocked]

Found a solution, for any that may come after me

Dim HasDecimal As Integer

HasDecimal = InStr(1, Me.Hours, ".15" Or ".30" Or ".3" Or ".45", vbTextCompare)

If HasDecimal <> 0 Then
        MsgBox "Hours must be entered in 15 minute increments using .25, .5 or .75 values.  Please try again.", vbOKOnly + vbExclamation
        Me.Hours = ""
        Exit Sub
End If