Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    185
    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. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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:
    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
  •