Len function not return proper values

[Mike]

I have a table with a phone number field. All numbers appear to be in the
format of 1234567890.

I've been having other issues with this field and ran a query on it. The
criteria i used was:

Len([Phone])=10

Out of 5500 records or so, it returned nothing. I figured i messed up the
function, so I researched it online and in the help file and determined
that, from what i can tell, my usuage is correct. So i changed it to:

Len([Phone])>7

I got 4 records out of the query.

Any ideas why it wouldn't be returning the length correctly - or at all?

 

[rquintal]

I have a table with a phone number field. All numbers appear to be in the
format of 1234567890.

I've been having other issues with this field and ran a query on it. The
criteria i used was:

Len([Phone])=10

Out of 5500 records or so, it returned nothing. I figured i messed up the
function, so I researched it online and in the help file and determined
that, from what i can tell, my usuage is correct. So i changed it to:

Len([Phone])>7

I got 4 records out of the query.

Any ideas why it wouldn't be returning the length correctly - or at all?

If the length of the phone number is 8, 9, 11 or more characters, the
first query will not return them, whereas the second one will.

 

[Tom Lake]

I have a table with a phone number field. All numbers appear to be in the
format of 1234567890.

I've been having other issues with this field and ran a query on it. The
criteria i used was:

Len([Phone])=10

Out of 5500 records or so, it returned nothing. I figured i messed up the
function, so I researched it online and in the help file and determined
that, from what i can tell, my usuage is correct. So i changed it to:

Len([Phone])>7

I got 4 records out of the query.

Any ideas why it wouldn't be returning the length correctly - or at all?

Is Phone a text field? What is the length of the phone field? Could there
be
leading or trailing spaces that throw off the Len function? Have you tried

Len(Trim([Phone])) = 10

Tom Lake

 

[Mike]

Ok, NOW for some reason =10 IS working.

However, >7 is not working. Last I check 10>7.

7 returns 4 fields where there were typo's and there are only 9 digits in
the field. But it isn't returning the thousands of other records that have
10 character in the fields.

The field is a text field and it in the table it is set to 10 characters. I
had the same results with setting it to 12 and 255.

I was using this function to see if I could figure out if there was
something wrong with my data because a combo box isn't functioning
correctly.

But as of now, Len([Phone])=10 works correctly. But using Len([Phone])>7 (or
any other number less than 10) only returns 4 records where there are only 9
digits in the field. It should be returning all of them, shouldn't it?

I have a table with a phone number field. All numbers appear to be in the
format of 1234567890.

I've been having other issues with this field and ran a query on it. The
criteria i used was:

Len([Phone])=10

Out of 5500 records or so, it returned nothing. I figured i messed up the
function, so I researched it online and in the help file and determined
that, from what i can tell, my usuage is correct. So i changed it to:

Len([Phone])>7

I got 4 records out of the query.

Any ideas why it wouldn't be returning the length correctly - or at all?

Is Phone a text field? What is the length of the phone field? Could there
be
leading or trailing spaces that throw off the Len function? Have you
tried

Len(Trim([Phone])) = 10

Tom Lake

 

[Mike]

it gets better...

Len([Phone])<9 returns records with values of 10!

WTF is going on?

Ok, NOW for some reason =10 IS working.

However, >7 is not working. Last I check 10>7.

7 returns 4 fields where there were typo's and there are only 9 digits in
the field. But it isn't returning the thousands of other records that have
10 character in the fields.

The field is a text field and it in the table it is set to 10 characters.
I had the same results with setting it to 12 and 255.

I was using this function to see if I could figure out if there was
something wrong with my data because a combo box isn't functioning
correctly.

But as of now, Len([Phone])=10 works correctly. But using Len([Phone])>7
(or any other number less than 10) only returns 4 records where there are
only 9 digits in the field. It should be returning all of them, shouldn't
it?

I have a table with a phone number field. All numbers appear to be in the
format of 1234567890.

I've been having other issues with this field and ran a query on it. The
criteria i used was:

Len([Phone])=10

Out of 5500 records or so, it returned nothing. I figured i messed up
the function, so I researched it online and in the help file and
determined that, from what i can tell, my usuage is correct. So i
changed it to:

Len([Phone])>7

I got 4 records out of the query.

Any ideas why it wouldn't be returning the length correctly - or at all?

Is Phone a text field? What is the length of the phone field? Could
there be
leading or trailing spaces that throw off the Len function? Have you
tried

Len(Trim([Phone])) = 10

Tom Lake

 

[KARL DEWEY]

Try it without the criteria --- Len([Phone]) --- and sort and see what gives.
--
KARL DEWEY
Build a little - Test a little

it gets better...

Len([Phone])<9 returns records with values of 10!

WTF is going on?

Ok, NOW for some reason =10 IS working.

However, >7 is not working. Last I check 10>7.

7 returns 4 fields where there were typo's and there are only 9 digits in
the field. But it isn't returning the thousands of other records that have
10 character in the fields.

The field is a text field and it in the table it is set to 10 characters.
I had the same results with setting it to 12 and 255.

I was using this function to see if I could figure out if there was
something wrong with my data because a combo box isn't functioning
correctly.

But as of now, Len([Phone])=10 works correctly. But using Len([Phone])>7
(or any other number less than 10) only returns 4 records where there are
only 9 digits in the field. It should be returning all of them, shouldn't
it?

I have a table with a phone number field. All numbers appear to be in the
format of 1234567890.

I've been having other issues with this field and ran a query on it. The
criteria i used was:

Len([Phone])=10

Out of 5500 records or so, it returned nothing. I figured i messed up
the function, so I researched it online and in the help file and
determined that, from what i can tell, my usuage is correct. So i
changed it to:

Len([Phone])>7

I got 4 records out of the query.

Any ideas why it wouldn't be returning the length correctly - or at all?

Is Phone a text field? What is the length of the phone field? Could
there be
leading or trailing spaces that throw off the Len function? Have you
tried

Len(Trim([Phone])) = 10

Tom Lake

 

[Mike]

I created an expression column

expr1: len([phone])

and it works correctly there.

Turns out the issue is, that since it is a text field, it's looking at the
values as

1
11
12
13
14.....
19
2
21
22......

Etc.

it's looking them up in the same was as A, AA, AB etc.

It was suggested elsewhere i use it in conjunction with the val function,
but val(len([phone]))>9 returns data mismatch errors. I'm not sure I can use
it that way. Making it it's own column works out well.

Try it without the criteria --- Len([Phone]) --- and sort and see what
gives.
--
KARL DEWEY
Build a little - Test a little

it gets better...

Len([Phone])<9 returns records with values of 10!

WTF is going on?

Ok, NOW for some reason =10 IS working.

However, >7 is not working. Last I check 10>7.

7 returns 4 fields where there were typo's and there are only 9 digits
in
the field. But it isn't returning the thousands of other records that
have
10 character in the fields.

The field is a text field and it in the table it is set to 10
characters.
I had the same results with setting it to 12 and 255.

I was using this function to see if I could figure out if there was
something wrong with my data because a combo box isn't functioning
correctly.

But as of now, Len([Phone])=10 works correctly. But using
Len([Phone])>7
(or any other number less than 10) only returns 4 records where there
are
only 9 digits in the field. It should be returning all of them,
shouldn't
it?


I have a table with a phone number field. All numbers appear to be in
the
format of 1234567890.

I've been having other issues with this field and ran a query on it.
The
criteria i used was:

Len([Phone])=10

Out of 5500 records or so, it returned nothing. I figured i messed up
the function, so I researched it online and in the help file and
determined that, from what i can tell, my usuage is correct. So i
changed it to:

Len([Phone])>7

I got 4 records out of the query.

Any ideas why it wouldn't be returning the length correctly - or at
all?

Is Phone a text field? What is the length of the phone field? Could
there be
leading or trailing spaces that throw off the Len function? Have you
tried

Len(Trim([Phone])) = 10

Tom Lake