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

  • Thread starter Thread starter Ellette
  • Start date Start date
E

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

KARL DEWEY said:
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


Ellette said:
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.
Click to expand...
Click to expand...
 
Just apply what I said to your linked .txt file.
--
KARL DEWEY
Build a little - Test a little


Ellette said:
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

KARL DEWEY said:
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


Ellette said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top