Link to home
Start Free TrialLog in
Avatar of Scott Palmer
Scott PalmerFlag for United States of America

asked on

MS Access. Using Instr to get the Suite part of an address

I need to split out an address with the the number and street in the 1st column and the Suite part in the 2nd column.  

The address is 2253 INDIAN RIVER RD STE 360  
I got it to work for the 1st column using: IIf([Address_1_2] Like "*STE*",Left([Address_1_2],InStr([Address_1_2],"STE")-1),[Address_1_2]).  The result was: 2253 INDIAN RIVER RD 
But I can't figure out how to get the 2nd part of the address: STE 360  
I tried using MID, but either it would not work or it would not include STE.

Avatar of ste5an
Flag of Germany image

Just use Right() for the rest.
Avatar of Scott Palmer


It did not work.  I substituted Right - If([Address_1_2] Like "*STE*",Right([Address_1_2],InStr([Address_1_2],"STE")-1),[Address_1_2]).  The result was: NDIAN RIVER RD STE 360 
Avatar of Scott Palmer
Scott Palmer
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial