2002-11-06, 14:10 #1
- Join Date
- Feb 2010
- Denver, CO
- Thanked 0 Times in 0 Posts
Empty text fields padded with spaces (AXP SP1)
I need to count the number of records that have a specific field populated. The field in question is a text field and defaults to spaces (11 of them) when the field is not populated.
I am querying an SQL Server database and cannot change the bahavior/properties of the tables.
How do I count only those records that have data in them and ignore those records where that field is filled with spaces??
I have tried the following with no success:
Is Not Null
Not Like " "
Not Like " *"
Does anyone have any other ideas?
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2002-11-06, 14:30 #2
- Join Date
- Mar 2002
- Thanked 16 Times in 16 Posts
Re: Empty text fields padded with spaces (AXP SP1)
Create a totals query and add the field. Set the Totals option to Count.
Next to it, enter the following in the Field line:
where you must substitute the actual field name, of course. When you exit this cell, it will be changed to something like
Set the Totals option to Where (this clears the View check box automatically) and enter
in the Criteria line. The Trim function removes all leading and trailing spaces, so that a value consisting of spaces only is reduced to an empty string "".