Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace and search (Access2000-03)

    I need to have remove ALL from ALL USA Locations value in a field.
    There are many other names so it is IIF field name is starts with ALL - start from 5th position...
    I need to use Replace function, correct?

    What is more important, can you, please, tell me when I am looking for stuff like that online OR in this forum - how do I word this correctly to type into a search box to come up with correct info?

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

    Re: Replace and search (Access2000-03)

    I would start by searching for replace expression and look at the first "hits" to see if there is anything useful. If not, you'd have to add another search term.

    The Replace function can be used in expressions in Access 2002 and later, but not in Access 2000. Here is a solution that should work in all versions of Access:
    - Create a query based on the table.
    - Add the relevant field to the query grid.
    - Enter the following in the Criteria row:
    <code>
    Like "ALL *"
    </code>
    - Switch to datasheet view to check that the correct records are returned, then switch back to design view.
    - Select Query | Update Query.
    - Enter the following in the Update to row, where FieldName is the name of the field.:
    <code>
    Mid([FieldName],5)
    </code>
    - Select Query | Run or click the Run button on the toolbar (the red exclamation mark).

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace and search (Access2000-03)

    I am using =Replace([Office name],"All","") and this is what I needed!
    Thanks, you gave me the right push.......

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

    Re: Replace and search (Access2000-03)

    You mentioned Access 2000-2003 in the subject. Please keep in mind that =Replace([Office name],"All","") won't work in Access 2000 - see ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace and search (Access2000-03)

    Yeah, it is 200 conveerted.

    Now I have a dillemma that I need to have this formula working with addition of IF Not Is Null and I can't adjust syntax...
    I am trying though...
    IIF(Not(IsNull(Replace(Office...and I get all errored...

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

    Re: Replace and search (Access2000-03)

    You shouldn't check whether the result of Replace is null, but whether the field itself is null:
    <code>
    =IIf(IsNull([Office name]),Null,Replace([Office name],"All ",""))
    </code>
    I added a space after All since you mentioned that you wanted to replace values such as "All USA".

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace and search (Access2000-03)

    Thanks, SO SO MUCH!

Posting Permissions

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