Len Function

[Jackie]

Would appreciate some help as I'm a novice Access user. I have a table with
employee numbers. The majority are 7 characters (text) that start with a
letter and then 6 numbers. However the remainder are 9 digit - all numbers.
I'm trying to extract only the 9 digit employee numbers. I thought the LEN
function would do it for me, but it's not working. Thanks

 

[kc-mass]

Paste this in the SQL view of the query design panel.

SELECT tblEmployees.EmplNumber, Len([EmplNumber]) AS LengthOf
FROM tblEmployees
WHERE (((Len([EmplNumber]))=9));

Regards

Kevin

 

[John Spencer]

Use criteria of
SELECT *
FROM tblEmployees
WHERE [EmployeeNumber] Like "#########"

Another option would be to select all the records where the employee number
did not start with a letter.
SELECT *
FROM tblEmployees
WHERE [EmployeeNumber] Like "[!A-z]*"

If you are a novice user then you would enter the criteria in the query under
the employee number fields as:
Like "#########"

Or you could use
Like "[!A-z]*"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Paste this in the SQL view of the query design panel.

SELECT tblEmployees.EmplNumber, Len([EmplNumber]) AS LengthOf
FROM tblEmployees
WHERE (((Len([EmplNumber]))=9));

Regards

Kevin

Would appreciate some help as I'm a novice Access user. I have a table
with
employee numbers. The majority are 7 characters (text) that start with a
letter and then 6 numbers. However the remainder are 9 digit - all
numbers.
I'm trying to extract only the 9 digit employee numbers. I thought the
LEN
function would do it for me, but it's not working. Thanks