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
> Problem With Nulls (again), Access 2002    
 
   
keep_lookingup
post Jan 10 2017, 06:13 PM
Post#1



Posts: 31
Joined: 14-December 12



I have been fighting null values and have done a lot of research about nulls on line and do understand that they can be a problem.

This problem:

In my table I have a value [stnum]=street address number ie. 2691
and [stname]=street name ie. Scott Crescent

Thinking I am going to be smart and make a new field in my query so it would be easier for me to see the total street address I have made a field

address:[stnum]+" "+[stname] to show me the street address as 2691 Scott Crescent

This works great until I get a [stnum] that is null but it has a [stname]

Instead of getting the [stname] in my new field [address] it gives me a null in the [address] field.

Any way around this? I know I could copy all the [stname] to any of the [stnum] fields as a work around but that seems a little awkward.

Thank you for your help,
Doug
Go to the top of the page
 
theDBguy
post Jan 10 2017, 06:24 PM
Post#2


Access Wiki and Forums Moderator
Posts: 70,845
Joined: 19-June 07
From: SunnySandyEggo


Hi Doug,

There are two ways to overcome the problem. Since you're trying to concatenate two fields into one, you may be better off using the concatenation symbol, which is the ampersand (&) rather than the plus sign (+), which propagates nulls and thus produce unintended results. Or, you can use the Nz() function to auto-convert a null value into a number of string.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Jan 10 2017, 06:59 PM
Post#3



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


a third option is to change the expression slightly, as

address:([stnum] + " ") & [stname]

the above capitalizes on the Null propagation from the + sign, to ensure that you don't get a leading space in the street name, when there is no street number. fyi, the same setup is often used for concatenating names from two or three name fields in a table, as

FullName: FirstName & (" "+MiddleInitial) & " " & LastName
FullName: (FirstName & " ") & LastName

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
keep_lookingup
post Jan 12 2017, 11:50 AM
Post#4



Posts: 31
Joined: 14-December 12



Thanks to you both.
It worked perfectly and I would never have figured it out.
Doug
Go to the top of the page
 
theDBguy
post Jan 12 2017, 11:56 AM
Post#5


Access Wiki and Forums Moderator
Posts: 70,845
Joined: 19-June 07
From: SunnySandyEggo


Hi Doug,

You're very welcome. Tina and I were happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Jan 12 2017, 10:29 PM
Post#6



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


oops, so much for giving good examples. let me try this again!

FullName: FirstName & (" "+MiddleInitial) & " " & LastName
FullName: (FirstName & +" ") & LastName

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th October 2017 - 05:36 AM