Pulling the town out of an address

[JHB]

Hi:

I have inherited a Table which has a single field containing the
name and address of a client in it in the form:

Name, street, apartment, city, state zip.

For purposes of statistics and analysis I need to tease out of that
one field the town and state names. Luckily, there are always the same
number of commas in the fiend even if there is no data between them! I
know how to do this in Excel by testing for commas, counting the
number of characters left or right and using that count to shift.
However I do not find the same kind of commands in Access. . Can
someone tell me how to do it in Access, please.

Thanks

John Baker

 

[Phil Hunt]

may ways to do this.This should work


Split("Name, street, apartment, city, state zip", ",") (3) ===> give you
town
Split("Name, street, apartment, city, state zip", ",") (4) ===> give you
state

 

[Bob Quintal]

m:

Hi:

I have inherited a Table which has a single field containing the
name and address of a client in it in the form:

Name, street, apartment, city, state zip.

For purposes of statistics and analysis I need to tease out of
that one field the town and state names. Luckily, there are always
the same number of commas in the fiend even if there is no data
between them! I know how to do this in Excel by testing for
commas, counting the number of characters left or right and using
that count to shift. However I do not find the same kind of
commands in Access. . Can someone tell me how to do it in Access,
please.

Thanks

John Baker

investigate the split() function available in Access Basic.

 

[Gene Wirchenko]

may ways to do this.This should work

Split("Name, street, apartment, city, state zip", ",") (3) ===> give you
town
Split("Name, street, apartment, city, state zip", ",") (4) ===> give you
state

Ah, gives state and ZIP on the last. It needs more processing.
You could split that result with " ".

This sort of processing is vulnerable to data errors.

Leading and trailing blanks should be trimmed.

Pathological data can cause trouble. For example:
Name: "John Smith, Jr." (the comma)
State: "North Carolina" (Two words will wreck a naive
split.)

Sincerely,

Gene Wirchenko

 

[JHB]

     Ah, gives state and ZIP on the last.  It needs more processing.
You could split that result with " ".

     This sort of processing is vulnerable to data errors.

     Leading and trailing blanks should be trimmed.

     Pathological data can cause trouble.  For example:
          Name: "John Smith, Jr." (the comma)
          State: "North Carolina" (Two words will wreck a naive
split.)

Sincerely,

Gene Wirchenko

Thank you.I will try that. The state is supposed to be the 2 letter
abbreviation, and I know how to test for an error in that.

Thanks again

John Baker

 

[JHB]

     Ah, gives state and ZIP on the last.  It needs more processing.
You could split that result with " ".

     This sort of processing is vulnerable to data errors.

     Leading and trailing blanks should be trimmed.

     Pathological data can cause trouble.  For example:
          Name: "John Smith, Jr." (the comma)
          State: "North Carolina" (Two words will wreck a naive
split.)

Sincerely,

Gene Wirchenko

In that setup, how do I refer to a field in a table in a query?