Len function oddity

[cboath]

If i have a field where all entries should be 10 characters in length (a phone number, no formatting), and the in the table, i set the length of the Phone field to be 10 characters, max, AND I make a query and in the Phone field of the query i say

Len([Phone])>7

That should return all records who's phone number is 8, 9 or 10 digits in length, correct?

Problem is, it is only returning records whose phone number is 8 or 9 digits in length. Not those with 10.

Using Len([Phone])>9 does not return the records with 10 digit length. The only way I can get those records to show up in the query is to state it:

Len([Phone])=10.

FYI, I created a field in the query Expr1:Len([Phone]) and it lists all those records as having phone numbers with a length of 10.

Last time I checked 10 was greater than 7. Does anyone have any idea why it wouldn't return those entries using >7?

 

[cboath]

it gets better...

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

WTF is going on?

 

[RuralGuy]

What version of Access are you using and on what OS?

 

[cboath]

It's 2007 and Vista.

Found the problem, though.

It's a text field, and it's looking at the len value as text as well.

It's looking up the values in the same format as A, AA, AB, etc..

1
10
11
12....
19
2
20
21
22....

etc.

In these cases 7>10.

 

[cboath]

Amend that.

Using Len([field])<Val(10) seems to be the proper formatting. Just using 10 (or any other number) seemed to have access treat it as the string 10 and not the number 10. Using val(10) as to what the length need to be greater or less than straightened it out.

 

[RuralGuy]

That's strange and sounds like a bug to me. Glad you got it sorted.