Use LEN to filter a field by string length?

[Philocthetes]

Hi, all,

I'm trying to pull rows where the string length in the target field is less than 10 characters. I thought this should work:
LEN([TABLENAME].[FIELDNAME]) < 10 entered in the Criteria row for the target field. Access 365 puts "" around the number 10 and yields no results. All the examples I've found do not have quotes around the number and none are specific about how/where to put the expression in Query Design View.

Any help would be much appreciated.

 

[Gasman]

Works for me? via the QBE window

SELECT tblSubmitter.Forename, Len([Forename]) AS Expr1
FROM tblSubmitter
WHERE (((Len([Forename]))<10));

 

[arnelgp]

don't forget to add "" at the end (for Null values).

Expr1: Len([Forename] & "")

 

[Philocthetes]

Thanks for the pointer, Gasman. Good point to think about nulls, arnelgp, but values are required for the field I'm working with.

I'm getting closer to expected results, but 365 is so 'helpful' sometimes that I'm rarely certain the SQL I see is being run as I see it...

 

[ebs17]

that I'm rarely certain the SQL I see is being run as I see it...

The statement you see in the SQL view is executed. Do you have this in front of your eyes?

The QBE is just a wizard that helps the ignorant to get reasonably working queries and relieves the more knowledgeable of some routine work.

 

[Philocthetes]

Oh, that was just me waxing on about my growing trust issues with today's over-networked, under-tested software environment. I'm in a heavily managed enterprise setting and the number of unknown interactions between 'products' and 'services' is surely incomprehensible to most human minds; my trust issues abound. Like why has Access 365 started trying to put "" around things when that breaks the query?

I admit not having seen the initialism QBE before; I had heavy Mikey UI language training back when they cared about that and still think Query Design view when I write about it it.