Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing spaces and dashes (Access2K)

    I need a little help. I have a field that contains data in various forms such as H8 110000, 47100200, 47 200000--, H8112123--, etc. I need to be able to convert these text fields into a consistent text field. I would like the field to be XXXXXXXX, Basically 8 characters in length. I need to know how to easily remove the spaces between the 2nd and 3rd character, and how to eliminate the dashes at the end. If there is an easy way, please let me know. I need to compare these values with another table for duplicates, and in order to do this, I wanted the values to be in the same format as the other table.

    Thanks in advance for any assistance.

  2. #2
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing spaces and dashes (Access2K)

    This way will work but maybe has a simpler way: First, assuming the field is named SYMBOL the syntax would be as follows:
    1st. query: FIELD1:left([SYMBOL],2) FIELD2:right([SYMBOL],6)
    2nd query: Pick up the the two fields in the 1st query and then pull the two fields together using the ampersand "&" i.e. NEWSYMBOL:[FIELD1]&[FIELD2]

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

    Re: Removing spaces and dashes (Access2K)

    Hello Marc,

    Can the values contain spaces in other positions, and if so, do you want to keep or remove these?
    Are the dashes always the 9th or higher character, or can they occur in the middle of a value - and if so, do you want to keep or remove them?

    If you want to remove *all* spaces, and truncate the value to 8 characters, it is quite easy:
    - Create a query based on the table.
    - Add the field you want to modify to the query grid. Let's say it is named Data.
    - Change the query to an update query using the Query menu or the Query Type button on the toolbar.
    - In the Change To row, under the field you want to modify, enter<pre>Left(Replace([Data]," ",""),8)</pre>

    - Run the query (Query/Run or the Run button or the toolbar - the red exclamation mark).

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing spaces and dashes (Access2K)

    Without having tried this just yet, would this not pull the dashes in on the second field? Some of the numbers do not have dashes at the end and some do. I guess I could first run a query that pulls the first 9 characters from the left, and then utilize your suggestion in order to eliminate the spaces. Unless you can think of a better way. I will let you know how this turns out.

    Thanks for the help.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing spaces and dashes (Access2K)

    Hans,

    When I tried this, I got the following error:

    Undefined function 'Replace' in expression

    This error was received when I tried your technique with the update query.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Removing spaces and dashes (Access2K)

    If this is a one-time project you should be able to make the changes from the user interface directly - otherwise you might want to look at the Instr() function.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing spaces and dashes (Access2K)

    That means you either have a compile error somewhere in your database or possibly a reference that is MISSING or bad on that machine. Replace() is a valid function in Access 2000 and higher but did not exist in Access97. I have seen similar kinds of errors pop up in queries on a machine where we installed a runtime AXP app with an Office Web Components reference set. The reference didn't show up as MISSING, but taking it out (we weren't using Data Access Pages anyhow) eliminated the query error about an defined function totally unrelated to web components. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing spaces and dashes (Access2K)

    Thanks for the help. I used a combination of Left and Right and joined the results as first indicated to eliminate all of the spaces and dashes.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing spaces and dashes (Access2K)

    Glad you found a way to do it, but you'll regret it if you don't track down the reason you got the error in the first place because it will pop up somewhere else.
    Charlotte

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Removing spaces and dashes (Access2K)

    Note: Functions introduced in VB/VBA 6.0 (including Replace function) cannot be used directly in query expressions in Access 2K; they are not recognized by query engine and you will get the "Undefined function" error. You'd have to create a user-defined wrapper function and then use the wrapper function in query. For a list of new functions introduced in VB 6.0 see the following MSKB article:

    INFO: New String and Format Functions in Visual Basic 6.0

    These functions will work correctly in query expressions in Access 2002 (XP).

    HTH

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

    Re: Removing spaces and dashes (Access2K)

    Ah, that must be the cause. I recently migrated from A97 to AXP, and completely skipped A2000... I tried Replace in a query and it worked; I didn't think to check the MSKB to see if it would work in A2000.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing spaces and dashes (Access2K)

    I know that's the conventional wisdom, Mark, but I've never had a problem using them in Access 2000 either. You do have to take care to put the field name in brackets, but I've used Replace in queries in A2k with no problems.
    Charlotte

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Removing spaces and dashes (Access2K)

    I still get error in A2K, as illustrated. I'm using A2K SR-1A, maybe this is fixed in SP-2 or SP-3?? I have no way of testing this hypothesis, because here at work IT installs a "standard" Office installation which you cannot upgrade, because the installation CD's are not available. (We're in process of "migrating" to new system where you will not be able to so much as install a screen-saver on your workstation, let alone an Office SP upgrade....)
    Attached Images Attached Images

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

    Re: Removing spaces and dashes (Access2K)

    The MSKB article ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions says:
    <hr>The FormatCurrency(), FormatDateTime(), FormatNumber(), FormatPercent(), InStrRev(), MonthName(), StrReverse(), and WeekdayName() functions are not supported as expressions.<hr>
    Replace is not mentioned.

    This is from ACC2000: Contents of the Readme File:
    <hr>Functions Broken Because Of vbDatabaseCompare

    The InStrRev, Split, Filter, and Replace functions may fail because of errors resulting from the use of vbDatabaseCompare. The workaround is to explicitly specify a comparison argument, for example, vbBinaryCompare or vbTextCompare.<hr>
    Does it help of you use

    Replace([CustomerID],"A","1",1,-1,1)

    The extra arguments are:
    1 = start, i.e. start from the beginning
    -1 = count, -1 means replace *all* occurrences
    1 = compare, 1 is vbTextCompare (you can't use symbolic constants in expressions in a query)

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Removing spaces and dashes (Access2K)

    <<We're in process of "migrating" to new system where you will not be able to so much as install a screen-saver on your workstation, let alone an Office SP upgrade....>>

    Sounds to me like the "HelpDesk" has taken over the company, and figured out that they won't ever have to do any work answering peoples questions, and can install new software, patches and so on whenever the mood strikes them. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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