Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Unanswered: How could you divide the information in a single field into 2?

    Let's say I have a field on my Main table, it contains the city name and the postal code in brackets. How do I make it so I could either delete all the postal codes in my field or divide this field into 2, one has all the cities and the other has all the postal codes?

    Thanks in Advance!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What does your data look like? (example please)!
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by RedNeckGeek
    What does your data look like? (example please)!
    Data looks like:
    Toronto (L2K 1Y5)
    Ottawa (K4J 3H3)
    ...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    run an update query that truncates the cityname at the left bracket

    have a look at the string functions
    especially the instr, left$,right$,mid$
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Here's some of the grunt work for you:

    Dim CityPostalCode as String


    CityPostalCode = "Ottawa (K4J 3H3)"

    left(CityPostalCode,( instr(CityPostalCode," ")-1)) returns Ottawa

    right(CityPostalCode,(len(CityPostalCode)-( instr(CityPostalCode," ")))) returns

    (K4J 3H3)
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    left(CityPostalCode,( instr(CityPostalCode," ")-1)) returns Ottawa
    true, but it also returns 'New' from 'New York (123456)'

    ?? safer:
    left(CityPostalCode,( instr(CityPostalCode,"(")-2))

    ??? safer and faster
    left$(CityPostalCode,( instr(CityPostalCode,"(")-2))

    ???? safest and faster:
    trim(left$(CityPostalCode,( instr(CityPostalCode,"(")-1)))

    and, even tho it is very ugly:
    mid$(CityPostalCode, instr(CityPostalCode, "(") + 1, instr(CityPostalCode, ")") - instr(CityPostalCode, "(")-1)
    returns '123456' from 'New York (123456)'

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    True, I only checked against his examples. Of course, New York doesn't have a Canadian postal code! LOL! Good catch, izy!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by izyrider
    left(CityPostalCode,( instr(CityPostalCode," ")-1)) returns Ottawa
    true, but it also returns 'New' from 'New York (123456)'

    ?? safer:
    left(CityPostalCode,( instr(CityPostalCode,"(")-2))

    ??? safer and faster
    left$(CityPostalCode,( instr(CityPostalCode,"(")-2))

    ???? safest and faster:
    trim(left$(CityPostalCode,( instr(CityPostalCode,"(")-1)))

    and, even tho it is very ugly:
    mid$(CityPostalCode, instr(CityPostalCode, "(") + 1, instr(CityPostalCode, ")") - instr(CityPostalCode, "(")-1)
    returns '123456' from 'New York (123456)'

    izy

    Since I am soo new in access. These are the codes for an update statement right? But I am getting syntax errors.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tialongz
    Since I am soo new in access. These are the codes for an update statement right? But I am getting syntax errors.
    Noooo ... Iz was demonstrating generically. You'll have to do something like:

    UPDATE SomeTable SET Whatever={Split Mechanism of Whatever Here}, WhateverElse={The leftovers cleaned up};

    And yes, I know, TOTALLY unhelpful ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by M Owen
    Noooo ... Iz was demonstrating generically. You'll have to do something like:

    UPDATE SomeTable SET Whatever={Split Mechanism of Whatever Here}, WhateverElse={The leftovers cleaned up};

    And yes, I know, TOTALLY unhelpful ...
    ahah..someone is cheerful this afternoon.

    Yes I did that.
    something like:
    The field with all the data: say EBusinesses
    I created 2 new fields: Businesses, BusinessID

    UPDATE [MAIN TABLE] SET [BUSINESSES] = mid$(EBusinesses, instr(EBusinesses, "(") + 1, instr(EBusinesses, ")") - instr(EBusinesses, "(")-1), [businessID]= No idea what goes here.

    Just for the record, treat me like someone who is trying to become an expert in access over night.
    Last edited by tialongz; 12-15-06 at 17:20.

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you have changed the rules a little.
    [businessID]= i also have No idea what goes here since you don't explain.

    going back to trying to parse something like:
    'New York (123456)'
    into:
    'New York' and '123456'

    Code:
    UPDATE TheTableName SET 
    City = trim(left$(CityPostalCode,(instr(CityPostalCode,"(")-1))), 
    ZIP = mid$(CityPostalCode, instr(CityPostalCode, "(") + 1, instr(CityPostalCode, ")") - instr(CityPostalCode, "(")-1)
    izy
    currently using SS 2008R2

Posting Permissions

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