Len Function

  • Thread starter Thread starter Jackie
  • Start date Start date
J

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
 
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
 
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

kc-mass said:
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


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