[MSAccessProfessionals] Basing an Expression off of Another Expression?

Hi Everyone!

 

I am wanting to update one of my databases to change how the address is input ….initially I had several fields that I used to signify an address. There was the Street Number (2244), the Street Direction (W), And the Street Name (Colfax Ave).  Since I often needed the entire address to refer to, I created a concatenated address as an Expression and named it ADDR.

 

Although hitting that Enter Key between the original fields doesn’t slow my roll any, it does so for other potential users.  I created a New Field named ADDRESS COMPLETE into which the entire address can be entered.  HOWEVER: I still want to have one place that contains the entire address whether it was entered the old way (the 3 separate Fields) or the new way (into the New Field named  ADDRESS COMPLETE).

 

Is there a way to build a New Expression (NEW ADDR), based off whether the Old Expression (ADDR), has an address in it or not. If any of the previously mentioned concatenated fields are filled out, then this expression  should give the info (ADDR).,  otherwise the expression should show the results from the New Field (ADDRESS COMPLETE).   I’ve tried various iterations of the following but I always get an error.  Is this doable? Thanks Everybody and Happy Holidays to All!

 

NEW ADDRESS: IIf(IsNull([ADDR]),[[ADDRESS COMPLETE]),[ADDR]