Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting City State Zip (XP SP2)

    Is there an easy way to separate out city, state and zip if they are currently in one field?

    TIA
    egghead

  2. #2
    New Lounger
    Join Date
    May 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting City State Zip (XP SP2)

    the easiest way i found is to export the field (via a query) to MS Excel. Once in excel there is a tool in the Data tab called text to columns. run that - check for multi-word city names, then import the new excel data to Access.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting City State Zip (XP SP2)

    Do you know how this might work on a large database?
    egghead

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

    Re: Splitting City State Zip (XP SP2)

    If it is always in the form New York, NY 10011, you can use these in a query:

    City: Left([FieldName], InStr([FieldName], ",")-1)

    State: Mid([FieldName], InStr([FieldName], ",") + 2, InStr(InStr([FieldName], ",") + 2, [FieldName], " ") - (InStr([FieldName], ",") + 2))

    Zip: Mid([FieldName], InStr(InStr([FieldName], ",") + 2, [FieldName], " ") + 1)

    where FieldName must be replaced by the actual name of the field.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting City State Zip (XP SP2)

    Thank you; this is what I'm looking for! Is it possible for you to explain what the sections are doing? I guess the -1 and +2, etc are confusing for me (what they actually refer to) and then in the state field there is a InStr nested in another one and I'm not sure why, but I know it works! And what does "InStr" stand for? <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    egghead

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

    Re: Splitting City State Zip (XP SP2)

    InStr is a function that searches for a substring in another string, and returns either 0 if the substring was not found, or the position of the first occurrence of the substring. For example:
    <code>InStr("Microsoft creations", "cr")</code> returns 3, because the first occurrence of "cr" starts at the third character in "Microsoft creations".
    <code>InStr("Microsoft creations", "cs")</code> returns 0, because "cs" does not occur in "Microsoft creations".
    You can also specify a starting position:
    <code>InStr(5, "Microsoft creations", "cr")</code> returns 11, because the first occurrence of "cr" from the 5th character starts at the 11th character.

    In the expression <code>Left([FieldName], InStr([FieldName], ",")-1)</code>, we look for the position of the comma, and subtract 1 in the Left function to return all characters before the comma, i.e. the city name.

    In the expression <code>Mid([FieldName], InStr([FieldName], ",") + 2, InStr(InStr([FieldName], ",") + 2, [FieldName], " ") - (InStr([FieldName], ",") + 2))</code> (by far the most complicated one), we use <code>InStr(InStr([FieldName], ",") + 2, [FieldName], " ")</code> to determine the position of the first space at least 2 positions after the comma. We then subtract the position 2 after the comma, i.e. the first of the state name, to get the length of the state part. Mid extracts the correct part from the field.

    The third expression <code>Mid([FieldName], InStr(InStr([FieldName], ",") + 2, [FieldName], " ") + 1)</code> again determines the position of the first space at least 2 positions after the comma, and uses Mid to return everything after that, i.e. the zipcode.

    Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: Splitting City State Zip (XP SP2)

    Here's a small function you could call to return the specific part of a string:

    <pre>Public Function ReturnWord(ByVal varIn As Variant, _
    intWordNum As Integer, _
    Optional varSeparator As Variant = " ") As String
    ' created by Charlotte Foust 10/2/2000
    Dim saWords() As String

    saWords = Split(varIn, varSeparator)
    'The array is zero-based so reduce
    'the word number by one
    ReturnWord = saWords(intWordNum - 1)
    End Function</pre>

    You would call this in calculated fields in your query like this:
    <pre>City: ReturnWord([FieldName],1, ",")
    State: ReturnWord([FieldName],2)
    Zip: ReturnWord([FieldName],3)</pre>


    The comma is specified as a delimiter in the City expression so that a comma won't be returned as part of the City. Since the function assumes a default separator of a space, there's no need to specify the delimiter for the other two parts. If the comma may or may not be there, you would need to use the default delimiter and then remove the trailing comma.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting City State Zip (XP SP2)

    The function makes it easier to do the calculated fields! But, dumb question, where do you put the function? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    egghead

  9. #9
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting City State Zip (XP SP2)

    Clear as mud, all right. Your "microsoft" examples help alot, though. Thanks for the explanation! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    egghead

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

    Re: Splitting City State Zip (XP SP2)

    Charlotte's function should be copied into a standard module (the type you create by clicking 'New' in the Modules section of the database window.)

    Since the function is defined as Public, you can use it in other modules, in queries and in expressions on forms and reports.

Posting Permissions

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