Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Change (2000)

    I have a field that contains entrys divided by a /. I need to be able to remove the / and the characters before the /. The number of characters before the / varies. Any suggestions?

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Change (2000)

    You need to use a combination of:
    <UL><LI>the Len() function to get the length of the string...
    <LI>the InStr() function to get the position of the slash...
    <LI>the Right() function to keep only the right part of the string... [/list]newString = Right(oldString,Len(oldString)-Instr(1,oldString,"/"))
    (Obviously changing the variable names, oldString and newString to those that apply... )

    HTH

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

    Re: Data Change (2000)

    Or, to use the same names as Alexya1:

    Create a query based on the table.
    Add the field to be changed to the query grid.
    Change the query to an update query.
    Enter the following expression in the 'Update to' row:

    Mid(oldString,Instr(oldString,"/")+1)

    (substitute the actual field name)
    Execute the update query.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Change (2000)

    I Guess i'm a little confused. The field is called "customer" The entries are similar to:
    jones/harry
    bc electric/reynolds hospital
    jtr/johns towing
    and any variation you can imagine. Would the expressions suggested work with these examples?? Thanks for your reply

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

    Re: Data Change (2000)

    Yes, it should work if you substitute "Customer" for "oldString".

    If you want to change the values in the Customer field, use an update query like I described. If you just want to return the part after the "/" in a query, without modifying the original values, create a calculated field

    Whatever: Mid([Customer],InStr([Customer],"/")+1)


    where Whatever is the caption for the calculated field.

Posting Permissions

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