Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    186
    Thanks
    0
    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
    >0
    Not Like " "
    Not Like " *"

    Does anyone have any other ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 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:
    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 "".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •