Specify number of characters in a field (2003)

Topic

New Reply

How can I set a validation in a table field to specify that the number of characters allowed in a field is 10? And, can I set this after there is already data in the db in the field?

Reply | Quote

Replies

You can set the Field Size property of the field to 10. When you save the table design, Access will warn you that text will be truncated. If you confirm, all values in the field will be truncated to a maximum length of 10 characters.

Reply | Quote

Thanks, but I want the field to have exactly 10 characters – no more, no fewer. So, how can I tell it not just a maximum, but exactly 10 characters.

Reply | Quote

OK, setting the Field Size to 10 will ensure that the user can’t enter more than 10 characters. To force the user to enter exactly 10 characters, enter the following in the Validation Rule property of the field:

Len([FieldName])=10

where FieldName is the name of the field. Also enter an appropriate warning text in the Validation Message property.

If the field already contains data when you save the table design with the new validation rule, Access will ask you if want to test the existing data. If you answer Yes, Access will tell you whether existing data violate the validation rule, but it won’t tell you which records this concerns. You can save the table design, and the validation rule will apply to everything you do with the field from then on, both in existing records and in new records.

Reply | Quote

Perfect! Thank you – that’s exactly what I wanted, and it works great.

Reply | Quote