Data Validation Rule - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Data Validation Rule

Dan Jackson

4 years ago

Hi,

I have a FirstName and LastName Field with this as a validation rule in the table.

Not Like "*[!-A-Z']*"

Its designed to only allow letters -'s and 's. My existing data of about 100 records fails this validation. Checking manually revealed nothing (Definitely no spaces).

I then tried running a query on the fields with

Like "*[!-A-Z']*"

...and came up with no results. Am I doing the query right? Is there any way to see whats causing the issue?
I've even tried going through the records, deleting all the data from the field so there is NO data and it is still failing.

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago

	Adam Schwanz @Reply 4 years ago
I'm not a fan of validation rule in most cases, If I can get away with it I'd rather use the replace function with a before/after update event. You can see about that here Replace Function The validation rule for how you want to do it though should be Not Like "[!a-z or ' or -]"

	Dan Jackson OP @Reply 4 years ago
Thanks Adam. I understand to be careful with the validation rule but would prefer to try and train the monkeys using the database! Tried your validation rule and no change. Also tried running the query using Like "[!a-z or ' or -]" Still nothing in the query. Cheers Though

	Kevin Robertson @Reply 4 years ago
Try this in your query. `Not Like "[!a-z or !' or !-]"`

	Dan Jackson OP @Reply 4 years ago
Done - it shows all the records with data but i need to find the data that breaches this rule (so in other words, reverse it so i can correct the records which are failing validation) Tried Like "[!a-z or !' or !-]" - No records show Really appreciate you time on this, cheers

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View