[AccessD] Put humpty dumpty back together again

Bob Gajewski rbgajewski at adelphia.net
Sat Jun 7 09:46:41 CDT 2008


John

Just wondering ... Do you need to concatenate these for a table, query, form
or report?

Bob Gajewski 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, June 07, 2008 09:20 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Put humpty dumpty back together again

I did not try it in the query itself.  I did try it in the database
properties.

John W. Colby
www.ColbyConsulting.com


Stuart McLachlan wrote:
> Have you tried setting explicitly immediately before your select?
> ie  SET CONCAT_NULL_YIELDS_NULL ON
> 
> 
> On 6 Jun 2008 at 10:43, jwcolby wrote:
> 
>> I found a database property / options / Concat null yields null, 
>> which it CLAIMS will change this behavior.  However it still yields a 
>> null regardless of whether I set this to true or false.
>>
>> Ths ISNULL() thing no doubt works but I have to concat 7 different 
>> fields for 84 million records.  I suspect that I would be setting 
>> myself up for a week long query.
>>
>> Given that the property makes no difference I may be forced to do 
>> this however.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> Paul Hartland wrote:
>>> Off the top of my head try:
>>>
>>> Select ISNULL(HouseNumberPrefix,'') + ' ' + ISNULL(HouseNumber,'') + 
>>> ' ' +
>>> ISNULL(HouseNumberSuffix,'') + ' ' + ISNULL(Direction,'') + ' ' +
>>> ISNULL(StreetName,'') + ' ' + ISNULL(Mode,'') + ' ' + 
>>> ISNULL(Quadrant,'') + ' ' + ISNULL(Appt#,'')
>>>
>>> Paul Hartland
>>>
>>> 2008/6/6 jwcolby <jwcolby at colbyconsulting.com>:
>>>
>>>> I have a database that has split the address line into
>>>>
>>>> HouseNumberPrefix
>>>> HouseNumber
>>>> HouseNumberSuffix
>>>> Direction
>>>> StreetName
>>>> Mode (N, NW etc)
>>>> Quadrant
>>>> Appt#
>>>>
>>>> I need to put Humpty back together again to feed off to Address 
>>>> Validation.  How would I do that in SQL?
>>>>
>>>> I THINK I can just append them all together with spaces between the 
>>>> parts and that would be fine EXCEPT that when you do something like 
>>>> NULL + SomeString you end up with null.
>>>>
>>>> How would I do what I am trying to do in SQL?
>>>>
>>>> --
>>>> John W. Colby
>>>> www.ColbyConsulting.com <http://www.colbyconsulting.com/>
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>>
>>>
>>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
> 
> 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list