Len function not working

[S_Preston]

Hello,

I have a short text field called [license] in a table that is set to 13 characters; the total number of records in this table is 323,992. I wish to capture only data in [license] where the data in this field is equal to 13 characters. I have 5932 records where the value is listed as 0. I have an unknown number of records where the value is less than 13 characters. I have attempted to create group by query where Len([license]) query where the criteria is 13 and License is displayed, that returned the same number 303,979 records. This query did not produce favorable results I can clearly see the License string has less than 13 characters. Does anyone have any suggestions? I realize that I can manipulate the data in Excel Len function but I cannot export 323,992 records at once into Excel. :banghead:

 

[NauticalGent]

In the query grid, try Len([liscense]) = 13

I cannot test it becuase I am not at my computer, but I THINK it will work.

 

[MarkK]

What happens if you get really basic, like...

SELECT Len(YourField) As LengthOfField
FROM YourTable

See how that's a query with a single calculated field showing the length of a single field in the table? That way you can determine if the Len() function is working, and if it is, start adding the features you need to that working query.

Alternatively, if you are having trouble with SQL, post the SQL.

hth
Mark

 

[bob fitz]

In the query grid, try Len([liscense]) = 13

I cannot test it becuase I am not at my computer, but I THINK it will work.

While you were posting I was testing. Can confirm that
Len([license]) = 13
should indeed work. BTY you have made a slight spelling mistake in the name of the field.

 

[NauticalGent]

Thanks Bob, my IPad is dyslexic!

 

[S_Preston]

Markk -

Your query produced results of "13" in all 323,992 fields.

 

[jdraw]

Perhaps, based on Markk's post, to see if there is some pattern???

SELECT YourField,Trim(Yourfield) as Trimmed, Len(YourField) As LengthOfField
FROM YourTable

 

[MarkK]

Markk -
Your query produced results of "13" in all 323,992 fields.

Perfect, that reveals a feature of the problem, so now riffing off jdraw's post...

SELECT Len(Trim(YourField)) As LengthOfYourFieldTimmed
FROM YourTable

Also, if you show us your actual SQL then as we tweak is, you can just copy and paste it, as an idea.
Mark

 

[arnelgp]

Add criteria to License:

Like "[1-9A-Z]????????????"

 

[NauticalGent]

Well S_Preston? The suspense is killing me...

 

[S_Preston]

NauticalGent, results provided did not provide me with the desired result. The field size is set to 13 characters, and always returns the same number of records 323,992.

 

[jdraw]

We need to see some actual SQL.
And some sample data.

 

[Mark_]

Can you copy the offending table to its own db, add in the same query you are trying to use, and test there? If it work separately you may have a corruption issue. If it doesn't, you can then zip and post the database to let us help find the issue.

 

[S_Preston]

MarkK, that worked!

SELECT Len(Trim(YourField)) As LengthOfYourFieldTimmed
FROM YourTable

 

[NauticalGent]

NauticalGent, results provided did not provide me with the desired result. The field size is set to 13 characters, and always returns the same number of records 323,992.

I have tried this on a few tables of mine and it has ALWAYS worked. Out of curiosity, are you using field masks?

 

[Minty]

This sounds (as NG has asked) as if the data has padding on it to always store as 13 characters.

In SQL server this would be a Char(13) type data and is fixed length and always stores 13 characters, instead of the normally preferred VarChar(13) which will store up to 13 characters.