Len expression as criterion?

[Duradel]

Hi there

I'm a rookie at using Access so sorry if this question is stupid but I just cannot find the explanation to my mistake.

I have a database of movies, one field being 'Title'. I want to make a query which will list all movies with a title of 10 or less characters.
I made a query with 1 field being title and used the following criterion:

Len([title])<11

It works, but it will only display titles of movies with 10 characters, not those with 5 characters.


My current workaround: make a new field in the query with Len([Title]) and <11 as a criterion. After that I hide this field from the query result.


This seems like a hassle, can anyone explain why the first method does not work?


A screenshot to explain the situation (sorry Dutch Access version): http://puu.sh/6qpZG.png


Thanks in advance!

[pbaldy]

Keep in mind that the design grid is just a user friendly interface to create the SQL that Access will actually use. Switch each of those methods to SQL view one at a time and look at the final SQL and I think you'll see the problem.

[Duradel]

Keep in mind that the design grid is just a user friendly interface to create the SQL that Access will actually use. Switch each of those methods to SQL view one at a time and look at the final SQL and I think you'll see the problem.

Thanks for your reply, but as I have little knowledge about coding. I was wondering why this works for some fields whereas it doesn't work for other lists.

The sql view for both is as follows:

(Faulty one: )

SELECT FILMS.Bandnr, FILMS.Titel
FROM FILMS
WHERE ((Len([titel])<="10"));

(Working one: )

SELECT FILMS.Bandnr, FILMS.Titel, Len([titel]) AS Expr1
FROM FILMS
WHERE (((Len([titel]))<=10));


So why do I need to add a third field in the query to add a criterion for the same table (or at least the same part 'Title' of it). The last part, which is most important I think, is exactly the same.
Also, the first method seems the work for other queries, but not for this one.

Does anyone know what could be causing this?

Thanks a lot

[Duradel]

Update:

For some reason Access places too many brackets in the code. Removing the brackets (I've put them in bold) solves the problem

WHERE (((Len([titel]))<=10));

I do not understand why this happens though, is this a bug in Access?

Thanks a lot :-)