Extract Data based on values

[Bronte1226]

OK I need to extract everything to the left of part of an address. For instance if the value for an address is "55 E Main St Unit 5" I need to remove everything to the left of "Unit 5" and put it into a new field "Address1". I am able to remove the "Unit 5" and place it into "Address 2" with this update query:

Mid(Customers![STREETNAME],InStr(Customers![STREETNAME],"Unit"),Len(Customers![STREETNAME])-6)

My tries to reverse this to capture the other side of this field have failed miserably...can anyone help a lost soul?

Many thanks...

 

[dhookom]

Try:
Left(Customers![STREETNAME],InStr(Customers![STREETNAME],"Unit")-1)


Duane
Hook'D on Access
MS Access MVP