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

Hi Duane!

 

Yes, in the Query that runs my main Form,  ADDR is a Field (Expression?).  It's a concatenation of multiple fields.  NEW ADDR is also a Field (Expression?) that I'm trying to create which will show the information form ADDRESS COMPLETE ( Field) if there is anything entered there, or will otherwise show the concatenated address contained in ADDR.

 

In the Query it looks like this:

 

ADDR: [ADDR Comm] & " " & [ADDR Number ] & " " & [ADDR St Dir ] & " " & [ADDR Street ] & "   " & [Cross St Comm] & "   " & [Cross St Dir] & " " & [Cross St] & "  " & [Cross St 2 Comm] & " " & [Cross St 2 Dir] & " " & [Cross St 2] & " " & [Precinct]

 

Crystal has also been trying to help me with this one.  She made the very valid complaint that my Field Names should not have any spaces, which is of course true, but unfortunately my dyslexic brain can't seem to read them when I write them that way! I know. My bad!  She did show some concatenated information to me based on the abbreviated info I had previously shown that is contained in ADDR. I still couldn't wrap my head around creating the entire expression though I had thought I might have to use the concatenated info rather than the Expression (Field?) ADDR.

 

For the most part, I've never had any problems with this database. (IT has confirmed this to me on several occasions! Ha ha!) Right now I'm just trying to refine it a bit so I can retire some day and hopefully users who have no experience other than inputting info into a database will be able to run it smoothly in my absence!  New Orleans is calling me!!

 

Best Wishes to All!

 

Penny

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Tuesday, December 13, 2022 5:19 PM
To: MSAccessProfessionals@groups.io
Subject: [EXTERNAL] 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]