Re: [MSAccessProfessionals] Basing an Expression off of Another Expression?



Hi Penny,

Is ADDR a calculated field in the table design? Is it actually stored in the table or just displayed in a query or text box?

There is a difference between Null and a zero-length-string. 

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Price, Penny E. - DSD Admin Support Asst III via groups.io <penny.price=denvergov.org@groups.io>
Sent: Tuesday, December 13, 2022 1:18 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Basing an Expression off of Another Expression?

 

 

Hi Duane,

 

Thanks for the suggestion, but I still couldn't get it to quite work.  If the Old Expression (ADDR) had content then the New Expression (NEW ADDR) has the correct information. But if the New Field (ADDRESS COMPLETE) has an entry then nothing shows up in the Nex Expression (NEW ADDR).  I'm baffled!

 

Any other ideas?

 

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Tuesday, December 13, 2022 11:32 AM
To: MSAccessProfessionals@groups.io
Subject: [EXTERNAL] Re: [MSAccessProfessionals] Basing an Expression off of Another Expression?

 

There is a Nz() function that might work:

New Address: Nz(ADDR, [Address Complete])

If ADDR is null then use the other field.

 

Duane

Price, Penny E. - DSD Admin Support Asst III



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]