Len() function problem in query

[Eljefegeneo]

Using Access 2010
In my query I want to select only those fields less than 10 characters. So I used the criteria Len([FieldA]) <10. But I don't get anything. Looking at the criteria it is now Len([FieldA]) < "10". So now I try it again with fields less than 15 characters with the criteria Len([FieldA]) < "15" and I get some results. But if I type Len([FieldA]) > 15 and getting "15" this time I am getting results of field lengths less than and more than 15 characters. So, none of this makes any sense to me. I've looked it up and even Microsoft says that you can use Len([CountryRegion]) > 10 for countries/regions whose name is more than 10 characters long. So, can someone tell me what I am doing wrong. Any why do the quote marks appear when it is a number?

 

[pbaldy]

What is your actual SQL? In design view the criteria would just be

<10

On a field with the Len() function in it.

 

[Eljefegeneo]

I may have inadvertently figured it out, but I am still very confused. So I have posted a sample of what I was trying to do.

In making a query for tbl, the field (only one there) is a list of radio stations. I wanted to figure out which ones were only four letters so I typed in the criteria as Len([Station]) <5. But when I ran it the first time it gave me all of the stations regardless of length of text. So I went into SQL mode and deleted the " from around the 5 and it worked. But it set the criteria in a second column in query design mode.

Qry1 doesn't give me the correct result, but qry2 and qry3 give me results based on the query criteria, after I modified the queries by deleting the quotation marks.

My question is, why do quotation marks appear on the first criteria; Len([Station])< "5", and why do I have to go into the SQL editor and make the changes; deleting the quotation marks from around the number?

Or does Len() as a criteria only work in a separate column?

This is very confusing to a relative novice such as me.

Thanks for your help.

 

[MarkK]

Data Types 101

Data has types. Some data is numbers, some is not. If we add letters, we end up sticking those letters together, so . . .

"a" + "a" = "aa"

. . . but then how do we do math if . . .

1 + 1 = 11

When you delimit characters with quotes, like "this is a string", then you indicate that you want those characters understood as letters, not numbers. And you can do math on letters, like, it makes sense that . . .

"a" < "b" = True

. . . but then you can experiment with what happens when you do . . .

4 < "5"

. . . and that's what happened in your case, the Len() function returns a number, and you tested it against a string, so the result is not really useful, or predictable.

And, an important feature of strings is that they compare starting at the first character, not a the decimal point, so . .

"100" < "99" = True

. . . because "1" is < "9", but . . .

100 < 99 = False

. . . for obvious reasons.

Data types.

 

[pbaldy]

Query1 as it should be:

SELECT tbl1.Station
FROM tbl1
WHERE Len([Station])<5

 

[Eljefegeneo]

Thank you both for the explanation. So even though I wanted to check the length of the string [Station], Access thought it was text so it added the quotes ""? I do realize that the correct is a pbaldy posted it: SELECT tbl1.Station
FROM tbl1
WHERE Len([Station])<5.

But I had a hard time getting rid of the quotation marks. So one does have to set the criteria in a separate column. Another lesson learned!

 

[pbaldy]

So one does have to set the criteria in a separate column.

In design view, yes. That's what I tried to say in post 2, though not very well.