Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: Remove blank string in query.

    This is no doubt a quick one for someone.

    What is the expression in a query to tell Access to eliminate rows from the query where a specific field has only an undefined number of spaces in it? i.e. it is not a null value, nor is it a zero length string- someone has hit the space bar x amount of times.

    Ta for your help.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Len(ltrim(field1))=0
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    I think I know where you are coming from but that's just replaced every value in the field with a zero. I've tried using the Nz in order to replace the variable length blank string with something else that I can specify but seems Nz works only on nulls...

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Okay, I'll be more specific

    IIF(Len(ltrim(field1))=0, "SomeValue", "OtherValue")
    Inspiration Through Fermentation

  5. #5
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    I don't know how to specify what appears to be a random number of stored spaces which I assume is the "some value" part and it again is replacing the data in all fields with the "other value" even if I specify an identifiable piece of data.

    The data is imported from another source. I can't get Access to store x amount of spaces in the field even with 'allow zero length' imposed- which is the logical approach! I'm not sure if the source database allows it! If I 'back space' in the field then the magical invisible 'whatever it is' disappears.

    However, I think I've cracked it with

    <> "" & ""

    in the query design grid which seems to exclude the records in question.

    Thanks for your help anyway.
    Last edited by jonesyfella; 01-15-07 at 10:13.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It looks like you're dealing with some kind of end of line character, not a random number of spaces.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Always fun dealing with other peoples data! Cheers.

Posting Permissions

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