Parse Address Field

[Lisa W.]

I am trying to create an Address Line 2 field; I need to parse the data after
the comma in example #1 and if there is an address with no comma, I would
like that field to be blank. The address information either has a comman
separating the 2nd part of the address or it simply appears with the street
number and name.

Example #1: 5 Executive Campus, Suite 100

Example #2: 3280 Northway Drive

Address Line 2 would look like: Suite 100 (example 1) or "blank" field
(example #2)

Any assistance is greatly appreciated.

Thanks,
Lisa W.

 

[KARL DEWEY]

Try this --
Line1: IIF(Instr([Address],",")<1, [Address], Left([Address],
Instr([Address],",")-1)
Line2: IIF(Instr([Address],",")<1, Mid([Address], Instr([Address],",")+1))

 

[Dale_Fye via AccessMonster.com]

Do you really need two separate fields, or could you simply replace the comma
with a carrage return / line feed, to move the text to a second line. Then,
in your form or report, set the Address text boxes "can grow" property to Yes.


Dale