Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    2

    Question Unanswered: Error With Replace Function

    Hello

    I have a need to remove white spaces from a field that can be zero length. When I use the REPLACE function I get an error on the zero length fields. I know I could update the fields first with a value but I am sure there is an easier way. Any help would be appreaciated ...

    SELECT Replace([Street1]," ","",1) AS Street1N
    FROM tblAddress;

    I can do something as follows but would like to be able to do it with a function (unsuccessful with an Iff stmt)

    SELECT Replace(Street1)," ","",1) AS Street1N, Street2, City, Zip
    FROM tblAddress
    WHERE Street1 IS NOT NULL

    UNION ALL

    SELECT Street1 AS Street1N, Street2, City, Zip
    FROM tblAddress
    WHERE Street1 IS NULL
    Last edited by vegas1974; 05-06-04 at 00:28. Reason: Added more content

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Try this:

    SELECT Replace(Nz([Street1],"")," ","",1) AS Street1N
    FROM tblAddress;

  3. #3
    Join Date
    May 2004
    Posts
    2

    Thanks

    Thanks worked create ... never used the Nz function before did some reading on it ... thx again

Posting Permissions

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