Data validation

[knight48]

I have a table that contains one field that MUST be 9 digits exactly.
I usually import this data from excel and rename the new table to be the one I need.
Is there a way that I can either:
Import data from excel and then do a check on this field for errors or
If I keep the table stucture and import the new data into this table, a way I can require the field to be 9 digits and error out those that are not?

Thanks

 

[SydneyGeek]

Assuming that your 9-digit field will be text...

Go into Design View in the table, select the field, and in the Validation Rule property at the bottom of the screen, put this:
Like "000000000"
You can also put a validation message that fires when an incorrect value is entered -- but you'll find this a pain if you are importing.

Save and return to Datasheet view.

To test, right-click the title bar and choose "Test Validation". It should tell you whehter any records violate the validation rule(s).

If you want validation at import time, set up your system so you import into the table (delete existing records first if you importing a full data set, otherwise just append the records). When you finish importing, check out the Import Errors table -- it will have been created if you had any issues. Offending records should be in there.

Denis

 

[knight48]

Many thanks!
Tried this and I keep getting errors that data failed the validation. I have gone in an checked that the data is indeed text and that currently all fields are the 9 digits needed. Any thoughts?

 

[ezakovich_35]

data validation

have you tried using expression builder in your data validation LEN(Variable name) = 9 as a rule?