Need help validating data

[pappy1271]

Have a field that is Field Size Double
Format Fixed
Decimal Places 2

People will be enter increments of time in quarter hour increments. ie... .25
I want to make sure that what they enter is divisable by .25 or is a quarter hour increment. Another words, If they enter 1.13 I want the rule to let them know that it must be in Quarter Hour increments.

Thanks for your help.

 

[psemianonymous]

1. Maybe use the Currency data type and use a custom format instead of the complicated Double w/ special constraints? This isn't related to your problem, but it's something to think about...

2. There is no easy way to ensure they enter in 15-minute increments. Maybe in the form, you can run code which will check whether the value is "valid", and update the value if not. Specifically, add an AfterUpdate() event to your textbox on your form which will run these checks and (optionally) update the value.

Another option is to fill a combobox with all available options (0.0, 0.25, 0.5, ...) and set Limit To List to True, thus quickly (and very nastily) fixing your problem. You can generate such a list using Excel and the Fill->Series command, creating a huge list in no time.

 

[Lightning]

Try this:

Private Sub YourField_BeforeUpdate(Cancel As Integer)
Select Case [YourField] - Int([YourField])
Case Is = 0.25, 0.5, 0.75
Exit Sub
Case Else
MsgBox "Invalid Time"
Cancel = True

End Select

End Sub

The Int() function returns only the Integer portion of the entry. So [YourField] - Int([YourField]) calculates to only the decimal portion of the entry in that field.

HTH
Lightning

 

[pappy1271]

Sounds great, I will try that tomorrow. All suggestions are greatly welcome.
What a great site!

 

[GDGarth]

Lightning,

Don't you also need a case where the result is 0? That way, if the time is exactly on the hour, it will be the same as the integer value.

I like your solution - it's simple. Should the code also wipe out the entry? Otherwise, won't the user be able to hit "OK" on the message box, and still enter the data?

Could you put the check for proper value in the table validation rules? Just for fun, I put this in a table and tried it, and it seemed to work. Doing it this way would mean you wouldn't have to add it to every form, assuming you need to do that:

[propno]-Int([propno])=0 Or [propno]-Int([propno])=0.25 Or [propno]-Int([propno])=0.5 Or [propno]-Int([propno])=0.75



propno is my field name. You can then specify the message that pops up when you violate the rule.

 

[Lightning]

GDGarth

Don't you also need a case where the result is 0? That way, if the time is exactly on the hour, it will be the same as the integer value.

You're right, I should have included the zero condition as well. :~/

Should the code also wipe out the entry? Otherwise, won't the user be able to hit "OK" on the message box, and still enter the data?

Hitting OK on the message box won't move the user out of the field. The Cancel = True statement keeps the cursor in the required field.

Could you put the check for proper value in the table validation rules?

Probably. My solution above was what first occurred to me, and since it answered the question I didn't look any further. Your solution of adding it to the validation rules probably is a better way to go if you are including the field on more than one form.

Lightning