Character Counts 1

[theif68]

Is there a way to get charater counts of a feild in a table.

So if I have a company feild I want to get a count on how many are under or over 50 charaters long.

 

[rjoubert]

SELECT YourTable.Company, Len([Company]) AS Expr1
FROM YourTable
WHERE (((Len([Company]))>50));

 

[theif68]

Thank you that is perfect. Is there a way to get counts of a field.

Say I run a query on the company field and i want a layout something like this. Where length is the number of characters and count is the number of records that are that length.

Lenth Count
10 1
20 5
30 3

 

[rjoubert]

SELECT Len([Company]) AS Length, Count(YourTable.Company) AS CountOfData
FROM YourTable
GROUP BY Len([Company]);

 

[theif68]

Thanks alot.

 

[theif68]

Can this be done in a Report? This way I can see counts on multiple fields instead of running multiple queries?

SELECT Len([COMPANY]) AS Length, Count(tblStandardLayout.COMPANY) AS CountOfData
FROM tblStandardLayout
GROUP BY Len([COMPANY]);

and

SELECT Len([TITLE]) AS Length, Count(tblStandardLayout.TITLE) AS CountOfData
FROM tblStandardLayout
GROUP BY Len([TITLE]);

 

[rjoubert]

You could do a UNION query with your two queries, though if you want to be able to keep your Company length counts and Title length counts separate, you will need to add an addition field to your queries.

SELECT 'COMPANY' as Type, Len([COMPANY]) AS Length, Count(tblStandardLayout.COMPANY) AS CountOfData
FROM tblStandardLayout
GROUP BY Len([COMPANY])

UNION

SELECT 'Title' as Type, Len([TITLE]) AS Length, Count(tblStandardLayout.TITLE) AS CountOfData
FROM tblStandardLayout
GROUP BY Len([TITLE])

 

[theif68]

Thanks alot.