Empty text fields padded with spaces (AXP SP1)

Topic

New Reply

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
>0
Not Like ” ”
Not Like ” *”

Does anyone have any other ideas?

Reply | Quote

Replies

Create a totals query and add the field. Set the Totals option to Count.
Next to it, enter the following in the Field line:
Trim([Fieldname])
where you must substitute the actual field name, of course. When you exit this cell, it will be changed to something like
Expr1: Trim([Fieldname])
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 “”.

Reply | Quote