Hi Penny
I think the problem is that ADDR can NEVER be Null. ADDR is derived from this expression:
[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]
Even if all the component fields ([ADDR Comm], [ADDR Number ], etc) are Null, the resulting expression will still contain all the spaces you have concatenated into the expression between the Null fields, so the final result will be " " – 15 spaces, if my count is correct! Of course, you can't see these spaces in the output of your query, but they are there all the same.
You could use the Trim() function to remove leading and trailing spaces from the result:
ADDR: Trim([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])
… but the result will still not be Null – it will be an empty, or "zero-length" string (ZLS). It test for this, IsNull will not work, so you must use Len(<string>)=0 or <string>=""
NEW ADDRESS: IIf(Len([ADDR])=0,[ADDRESS COMPLETE]),[ADDR])
If you really want ADDR to be Null if all its components are Null, then you can use a trick that exploits a subtle difference between the & and + operators. It goes like this:
Null & "some string" -> "some string"
and
"some string" + "some string" -> "some stringsomestring"
but
Null + "some string" -> Null
Also:
Null & Null -> Null
In other words, + behaves the same way as & unless one of the operands is Null, in which case the result is Null.
You can then combine that with brackets in the expression:
(Null + "string1") & (Null + "string2") & (Null + "string1") -> Null
but
(Null + "string1") & ("ABC" + "string2") & (Null + "string1") -> "ABCstring2"
Putting all this together, you could change your original expression 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]
That way, ADDR will truly give Null if all the components are Null. It will also have the advantage of removing all the unwanted spaces between components if some, but not all of them are Null.
I hope this has given you some ideas!
Best wishes,
Graham
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Friday, 16 December 2022 06:30
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Basing an Expression off of Another Expression?
I wonder if you are using the expression:
NEW ADDRESS: IIf(IsNull([ADDR]),[[ADDRESS COMPLETE]),[ADDR]
in the same query as you are defining ADDR? The "ADDR" is referred to as an "Alias". I never use an alias in an expression in the same query as it is created. I always repeat the complete calculation rather than the alias. Also, ADDR will never be Null based on your concatenation which returns at least 10 spaces even if all the combined fields are null. In the following expression, if [ADDRESS COMPLETE] is null, the concatenation will be displayed.
NEW ADDRESS: Nz([ADDRESS COMPLETE],[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])
Subject: 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: Duane Hookom
Subject: [EXTERNAL] Re: [MSAccessProfessionals] Basing an Expression off of Another Expression?
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.
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?
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]
