Multiple Criteria in an Expression

[summer76]

I have billing addresses that come in all kinds of formats. Basically, I'm supposed to move anything that resembles a secondary address (such as suite, floor, #, room, etc.) to the secondary address column. I can do an expression for one criteria at a time (eg. suite only), but can it be done in one expression to move more than one instance (suite and floor)?

Eg. data:
123 Whopping Loop Suite 1
123 Whopping Loop #6
123 Whopping Loop Drive
123 Whopping Loop Ste 4

Eg. of my code for those with # indicating a secondary addr:
IIf(InStr(1,Trim([GEORGIA3]![Billing Addr1])," #")>0,Right(Trim([GEORGIA3]![Billing Addr1]),Len(Trim([GEORGIA3]![Billing Addr1]))-InStr(1,[Billing Addr1]," #")),[GEORGIA3]![Billing Addr2])

Anyones help is greatly appreciated!

 

[TomCologne]

This is not beautiful, but it works:

Secondary Unit:

Street2:IIf([Address] Like "*Ste" & " " & "*",Right([Address],Len([Address])-InStr(InStr([Address]," "),[Address],"STE ")+1),IIf([Address] Like "*Unit" & " " & "*",Right([Address],Len([Address])-InStr([Address],"Unit")+1),IIf([Address] Like "*Flr" & " " & "*",Right([Address],Len([Address])-InStr([Address],"Flr")+1))))

Street (w/o Secondary Unit):

Street:IIf([Address] Like "*" & " " & "STE" & " " & "*",Left([Address],InStr([Address],"STE ")-1),IIf([Address] Like "*" & "UNIT" & " " & "*",Left([Address],InStr([Address],"UNIT ")-1),IIf([Address] Like "*" & " " & "FLR" & " " & "*",Left([Address],InStr([Address],"FLR")-1),IIf([Address] Like "*" & " " & "SPACE" & " " & "*",Left([Address],InStr([Address],"SPACE")-1),[Address]))))

Results do of course depend on the consistency of the provided address string.


TomCologne