How do I validate a field has 5 characters (alphanumeric)?

[Ellette]

Importing data from Paradox file. Need to remove records with a Facility
identification equal to 3 characters. Data in this field can be 3 characters
or 5 characters. Nothing else. After removing all records with 3 character
length in Facility identification, I should have only records with a 5
character length Facility identification.

 

[KARL DEWEY]

If you link the Paradox file you can set criteria for a calculated field --
Len([Facility]) to only allow those that are 5 characters.

 

[Ellette]

I'm establishing a process that will in the future use .txt files from which
to import data, so I am not to use Paradox for any of this. (Probably should
haven't used it for importing either.)

I've tried validation rules, delete queries... I can't get anything to work
within Access.
Ellette

If you link the Paradox file you can set criteria for a calculated field --
Len([Facility]) to only allow those that are 5 characters.
--
KARL DEWEY
Build a little - Test a little

Importing data from Paradox file. Need to remove records with a Facility
identification equal to 3 characters. Data in this field can be 3 characters
or 5 characters. Nothing else. After removing all records with 3 character
length in Facility identification, I should have only records with a 5
character length Facility identification.

 

[KARL DEWEY]

Just apply what I said to your linked .txt file.
--
KARL DEWEY
Build a little - Test a little

I'm establishing a process that will in the future use .txt files from which
to import data, so I am not to use Paradox for any of this. (Probably should
haven't used it for importing either.)

I've tried validation rules, delete queries... I can't get anything to work
within Access.
Ellette

If you link the Paradox file you can set criteria for a calculated field --
Len([Facility]) to only allow those that are 5 characters.
--
KARL DEWEY
Build a little - Test a little

Importing data from Paradox file. Need to remove records with a Facility
identification equal to 3 characters. Data in this field can be 3 characters
or 5 characters. Nothing else. After removing all records with 3 character
length in Facility identification, I should have only records with a 5
character length Facility identification.

 

[John Spencer]

If you only want fields that have 5 characters, try the following as criteria.

WHERE [Facility Identification] Like "?????"

If that still gives you records with three character facility identifications
then you probably have trailing spaces in the fields, so try

WHERE TRIM([Facility Identification]) Like "?????"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County