UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Concatenation of 'empty' fields    
 
   
MrChips
post Jul 3 2005, 05:39 AM
Post#1



Posts: 387
Joined: 3-July 05
From: Tonbridge, Kent, England


I use 4 address rows. Adress1, Address2 ,Town, Post code
ddress2 is sometimes unused.
If I try to use address-line: =[address1]+", "+[addess2]+", "+[town]+", "+[postcode]+"."
then it is OK if all fields have something entered.
If address2 (say) is blank, I get nothing.
How can I get round this please?
regards
Go to the top of the page
 
tunkefer
post Jul 3 2005, 06:10 AM
Post#2



Posts: 568
Joined: 18-February 04
From: Cleveland, OH


In your example, address2 probably contains Null instead of a blank so the concatination fails on an invalid reference. Try this:
ddress-line: =Nz([address1],"")&", "& Nz([addess2,"")&", "& Nz([town],"")& ", "& Nz([postcode],"") & "."
The Nz functions substitue a blank string if the values are Null, otherwise they use the field's value.
TomU
Go to the top of the page
 
MrChips
post Jul 3 2005, 06:53 AM
Post#3



Posts: 387
Joined: 3-July 05
From: Tonbridge, Kent, England


I have never heard of Nz before.
I am most obliged to you.
I will try the advice staright away.
Many thanks
Can I use the same function in a report?
regards
dfp
Go to the top of the page
 
R. Hicks
post Jul 3 2005, 07:53 AM
Post#4


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


Welcome to Utter Access Forums ... frown.gif
This is what you need instead of the Nz() function ...

CODE
=[address1] & ", " & ([address2] + ", ") & [town]  & ", " & [postcode] & "."

In the above example .. if "address2" is Null .. the Null, comma, and space will be ignored that is within the parenthesis ...

RDH

Edited by: R. Hicks on Sun Jul 3 14:17:45 EDT 2005.
Go to the top of the page
 
HiTechCoach
post Jul 3 2005, 01:15 PM
Post#5


UtterAccess VIP
Posts: 18,992
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Ricky left out an & after the first , and the address2

should be:

CODE
=[address1] & ", " &  ([address2] + ", ") & [town]  & ", " & [postcode] & "."


Note: & and + do not worj the same. Normally you should use the &
Go to the top of the page
 
R. Hicks
post Jul 3 2005, 01:18 PM
Post#6


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


Thanks Boyd ... I have atered my reply to reflect the change.
DH
Go to the top of the page
 
HiTechCoach
post Jul 3 2005, 02:34 PM
Post#7


UtterAccess VIP
Posts: 18,992
Joined: 29-September 03
From: Oklahoma City, Oklahoma


Keep up youe great work Ricky. I got you back! uarulez2.gif
Go to the top of the page
 
JohnDurbin
post Jul 3 2005, 09:22 PM
Post#8



Posts: 234
Joined: 9-April 04
From: Eastern North Carolina


That's pretty cool! I had always used an IIF function to handle a possible null value in address2.
TW, CONGRATULATIONS FOR YOUR RECOGNITION AS MVP!!!...JD
Go to the top of the page
 
R. Hicks
post Jul 3 2005, 09:27 PM
Post#9


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


Thanks John .. wink.gif
This method can also be used when you concatenate mutiple lines in a txtbox such as:
ClientName
Address1
Address2
City, State
ZipCode
Using the method I posted .. it would eliminate an empty line should Address2 be a Null ...
RDH
Go to the top of the page
 
JohnDurbin
post Jul 3 2005, 10:44 PM
Post#10



Posts: 234
Joined: 9-April 04
From: Eastern North Carolina


How so? I was currious to see it work in a report but the best I could do with your method was three textboxes:
[First] & " " & [Last]
=[Address1] & Chr(13) & Chr(10) & ([Address2]+"")
=[City] & ", " & [StateOrProvince] & " " & [PostalCode]
Whereas using IIF I can have one textbox with:
=[First] & " " & [Last] & Chr(13) & Chr(10) & [Address1] & Chr(13) & Chr(10) & IIf([Address2],[Address2] & Chr(13) & Chr(10),"") & [City] & ", " & [StateOrProvince] & " " & [PostalCode]
For the sake of my continued education, what did you have in mind?...JD
Go to the top of the page
 
R. Hicks
post Jul 3 2005, 11:04 PM
Post#11


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


This should do what you need in a single txtbox ...

=[First] & " " & [Last] & Chr(13) & Chr(10) & [Address1] & Chr(13) & Chr(10) & ([Address2]+Chr(13)+Chr(10)) & [City] & ", " & [StateOrProvince] & " " & [PostalCode]

The above is a one single line expression ...
If Address2 is a Null value .. the line is skipped ...

RDH
Edited by: R. Hicks on Tue Jul 5 9:31:02 EDT 2005.
Go to the top of the page
 
JohnDurbin
post Jul 5 2005, 07:50 AM
Post#12



Posts: 234
Joined: 9-April 04
From: Eastern North Carolina


Excellent. When I tried it I used & for the CR and LF within Address2 and got an unrecognized square character within the address.
Thanks for showing me this new technique...JD
BTW, you left out an ampersand before [City].
Go to the top of the page
 
R. Hicks
post Jul 5 2005, 08:30 AM
Post#13


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


You are welcome John .. wink.gif
Is for the missing ampersand .. I did that to keep you on your toes ... LOL
I will edit my reply and fix it ..
RDH
Go to the top of the page
 
MrChips
post Jul 9 2005, 02:40 AM
Post#14



Posts: 387
Joined: 3-July 05
From: Tonbridge, Kent, England


Thank you all for your help with this , to you all perhaps, rather pathetic query.
I am now able to produce the concatenation that I wanted. ... many thanks.
On top of that the solution of enclosing round brackets around a list as described by R.Hicks has also solved a formatting problem that I gave up solving years ago. I just put up with the 'empty' line that occurred with an empty field.
Why have I never before thought of asking advice on a forum such as this?
Thank you all for taking the time to assist.
regards
Mr.Chips, Tonbridge, England
Go to the top of the page
 
R. Hicks
post Jul 9 2005, 08:59 AM
Post#15


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


That is Great!!!
You are very Welcome for the help .. wink.gif
DH
Go to the top of the page
 
mickowit
post Apr 23 2009, 02:32 PM
Post#16



Posts: 58
Joined: 16-November 04
From: Knoxville, TN, USA


so.. following this thread, I came up with the following for addresses on a report:
[Address Line 1] & ([Address Line 2]) & [City] & "," & [State/Province] & [Zip/Postal Code] & [Country]
The data is there but is lacking any line breaks. How do you do this so that after each field a line is created? Also, how do you avoid printing your own country i.e. skipping if = to....?
Go to the top of the page
 
JohnDurbin
post Apr 23 2009, 02:59 PM
Post#17



Posts: 234
Joined: 9-April 04
From: Eastern North Carolina


Carriage Return and Line Feed are:
hr(13) & Chr(10)
so you need something like:
=[Address Line 1] & chr(13) & chr(10) & ([Address Line 2]) & [City] & "," & [State/Province] & " " & [Zip/Postal Code] & chr(13) & chr(10) & [Country]
NOTICE THAT I PUT TWO SPACES AFTER THE STATE/PROVINCE.
BTW, you probably shouldn't use slashes as part of a name for a field....JD
Go to the top of the page
 
mickowit
post Apr 24 2009, 10:06 AM
Post#18



Posts: 58
Joined: 16-November 04
From: Knoxville, TN, USA


Here's a dumb one. Where do I stick that? I thought it was the control source, but apparantly not.
Go to the top of the page
 
JohnDurbin
post Apr 24 2009, 12:32 PM
Post#19



Posts: 234
Joined: 9-April 04
From: Eastern North Carolina


Have a look at the attached...JD
Attached File(s)
Attached File  Example_ConcatenateAddress1and2.zip ( 12.37K )Number of downloads: 13
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:09 PM