Creating a Validation Rule

[deejabram]

I need help! I am trying to create a validation rule for a number data type field. My rule would only allow entries to end with the following: .00, .25, .50, .75

The number before the decimal could range anywhere between 1 and 18.

Essentially, I only want users to be able to enter data types such as
1.25 or .25 or 17.75 etc...

Can someone please assist?

 

[vbaInet]

I don't think you need to apply any validation rule here.

In the table:

1. Set the datatype to Number
2. Change the Field Size to Double
3. Set Decimal Places to 2
4. I think the Format you would need is Fixed or you can create your own custom format.

If an error occurs on the FORM related to a user entering text etc, trap it using the error handler and display in a msgbox.

Welcome to the forum by the way

 

[deejabram]

Thanks for both the welcome and the advice.

Wouldnt the instructions you gave allow users to enter numbers ending in other than .00, .25, .50, or .75? For example, Access would allow them to enter 12.45 using these settings?

I am creating a database to track OT hours used throughout a Division. Ideally I want the Admin Assistants to enter data in a certain format. Hours used would be entered as per the below example:

4.25 (.25 representing 1/4 an hour)

Sometimes these Assistants do not think about what they are doing and instead enter

4.15 (15 representing minutes)

The same holds true with .75 (3/4 an hour) and .45 (minutes)

I would like to standardize their entries so that they are only permitted to enter the numbers in one way, so can be accurately calculated in a summary...


Thanks again for your assistance.
DJ

 

[ghudson]

You could create two combo boxes, one for hour and one for the minutes and set the allowed values. Then concatenate the two combo boxes into the text field bound to your table.

Or in the before or after update event of the text box you could use the Left() and Right() functions to test the values and ensure they meet your criteria.

 

[vbaInet]

I don't really do my validation on table level, so like ghudson mentioned, it would be much better it's done in the form.

But you can try this validation rule in the table if you want:

Right([fieldname], 2)=00 Or Right([fieldname], 2)=25 or Right([fieldname], 2)=50 or Right([fieldname], 2)=75

 

[deejabram]

Excellent. The above posts really helped a lot. Thanks for the assistance and the education.

DJ

 

[vbaInet]

Glad we could help.