CODEWITHSUNDEEP
ms-access
Shubharaj Buwa
Shubharaj Buwa

Reputation: 9

Add validation to numbers stored as text in Access

Field name in MS Access table - s1 data type - text

I use this field to store student marks.

'AA' will be entered if the student is absent and therefore I have selected text data type for this field

I want to restrict data entry to minimum marks = 0 and maximum = 75

How do I enter validation rule in this text field?

Upvotes: 0

Views: 320

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123654

You may want to reconsider your decision to store the values as Text. You could store them as Numeric and just use a custom Format property to display AA when the value is Null (which is allowed when "Required=No"):

Untitled.png

Upvotes: 2

Matt Hall
Matt Hall

Reputation: 2412

In design view for your table that will store the student mark, select the student mark field.

In the field properties in the lower part of the screen set the Field Size to 2, the Validation Rule to "AA" Or Between "0" And "75" and then in the Validation Text field, write a helpful message to the user describing the valid data they can enter in to this field:

enter image description here

If the user tries to put in data outside the restrictions of your validation rule, they'll see your validation text message and won't be able to save their invalid data:

enter image description here

Upvotes: 0

Gustav
Gustav

Reputation: 55841

Set maximum length of the text of the field to: 2

Set validation rule to: "AA" Or Between "00" And "75"

Also, apply an inputmask to force two characters, or make sure that values between 0 and 9 are entered as 00 and 09.

Upvotes: 1

Related Questions