Query Using Len()

[marvin67]

I would like to query a records to get the the records where number of character in a field is less than a certain number. For example

If i used this criteria: Len([firstname]) = "8", then it will produce all the records where all firstname have 8 character.

But if i used this criteria: Len([firstname]) < "9" , I don't get the result that I need where the number of character in the firstname is less than 9.

I would really appreciate your help.

 

[RichO]

Add a new column to the query in design mode.

Under field enter: =Len([FirstName])

Access will automatically turn that into : Expr1: Len([FirstName])

Then under criteria enter: < 9

You will probably want to "uncheck" the show box so the numbers don't display.

 

[marvin67]

Thank you very much for your help. That's works!!!

 

[bull57]

Add a new column to the query in design mode.

Under field enter: =Len([FirstName])

Access will automatically turn that into : Expr1: Len([FirstName])

Then under criteria enter: < 9

You will probably want to "uncheck" the show box so the numbers don't display.

I have a similar problem but sadly that is not the fix for me. I already had my query programmed in that way but I am still experiencing a problem. As this is just testing, I am going to post my code below:

SELECT Len([MAIN].[ADDRESS]) AS LEN, MAIN.ADDRESS
FROM MAIN
WHERE (((Len([MAIN].[ADDRESS]))>"28"));

As you can see, I am trying to get the query to return results where the length of the address field is greater than 28. It works fantastic on about 95% of my records. However, I am still getting results in my query that have a length much less than 28. Most of them no larger than 9. Here is the resulting query:

LEN ADDRESS
9 56 LEE ST
9 PO BOX 74
9 PO BOX 62
9 PO BOX 32
9 POBOX 135
8 PO BOX 7
8 92711 SW
9 PO BOX 74
9 2 KIVY ST
9 617 PEKRI
6 BOX 20
9 PO BOX 74
8 PO BOX 3
9 PO BOX 94
9 PO BOX 95
9 2307 N ST
9 PO BOX 71
9 55 ELM ST
9 PO BOX 16
9 602 RIVES
9 PO BOX 82
9 2206 YUMA
9 744 A AVE
9 PO BOX 31
9 PO BOX 37
9 PO BOX 67
9 PO BOX 82
9 PO BOX 51
5 N AVE
9 PO BOX 54
9 PO BOX 52
9 PO BOX 32
9 1219 E ST
8 ERIE AVE
9 PO BOX 87
8 PO BOX 3
9 PO BOX 13
9 PO BOX 57
8 PO BOX 9

You can see that none of those records should be returned. Could you provide me with any insight on why this is happening? Thank you in advance for any help.

(Before you ask, LOL, yes I am trying to return a query with no results. This query is going to serve as a validation check because I do not want ACCESS to automatically trim the field when I append records to the table.)

 

[pr2-eugin]

Any reason why the 28 is wrapped in double quotes?

 

[bull57]

I hate it when I do something stupid like that.

Worked fine. Kept auto-correcting to that in Design View, changed it in SQL view. :banghead:

Thanks.