Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strip empty space (Office XP)

    Hi,

    I am using an append query to merge the data from two fields [suburb] and [city] into one field called [suburb/city]. This is working really well, thanks to a tip from the lounge last week. I have noticed however, that if there is data in the city field but not the suburb field then there is a space to the left of the data. The instruction i am using in the append query is =[maindataentry]!suburb&" "&[maindataentryentry]!city. I can see where the space is coming from, is there an easy way I can run another update query and strip it out?

    Thanks....Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip empty space (Office XP)

    You can use + instead of & to concatenate the suburb and the space:

    ([maindataentry]!suburb+" ") & [maindataentryentry]!city

    The difference is that Null & "something" = "something", but Null+"something" = Null. Another method would be to use IIf and the IsNull function to test if the suburb is empty:

    [maindataentry]!suburb & IIf(IsNull([maindataentry]!suburb,""," ") & [maindataentryentry]!city

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip empty space (Office XP)

    Thanks!!!!

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip empty space (Office XP)

    =Trim([maindataentry]!suburb&" "&[maindataentryentry]!city)

    That way, it doesn't matter if either are blank, there will be no extra spaces on either side.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip empty space (Office XP)

    Great, thanks very much, another good idea!!

    Regards.....Peter

Posting Permissions

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