Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Location
    Florida
    Posts
    4

    Unanswered: Removing spaces within a text field

    This is a newbie question but I have to ask as I cannot figure this one out and it is driving me insane.

    I have a text field which contains data that has spaces that I am trying to remove. This space can be near the beginning or near the end of the text. Examples below:

    A2 23041 0
    A2-E 43644 0
    A80A041451 0
    K2 252 8 0
    P3193211 0

    This more or less covers all of the various ways the data can appear. I have tried a simple replace just to see if it evens finds the space but no joy.

    If there are any suggestions as to how I can remove the spaces, it would be greatly appreciated. I am using Access 2003..

    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    [field1] = Replace([field1], " ", "")

    That should work depending on the event you want it to run on.

    Alternatively come chip in on this thread http://www.dbforums.com/showthread.php?t=1614744

    the best way to remove spaces from a table is to remove them before they append to it - that's what validation is for!

    If you want to test a field for spaces then try:
    IF [field1] LIKE "* *" THEN
    ...
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2006
    Location
    Florida
    Posts
    4
    Quote Originally Posted by georgev

    the best way to remove spaces from a table is to remove them before they append to it - that's what validation is for!
    That is a battle I have to take up with the IT department.. in my world that means writing up a ten page dissertation on why they need to fix it so that I don't have to spend an hour trying to clean up the data when I get it. Even then it will take six weeks and a blood sacrifice to some heathen god before they make the change.

    Thanks for the tip though, I will try that out.

    EDIT: good to go.. that worked just fine. Now to get to writing my data dissertation.
    Last edited by demonR6; 02-06-07 at 10:11.

Posting Permissions

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