[AccessD] Put humpty dumpty back together again

jwcolby jwcolby at colbyconsulting.com
Sat Jun 7 10:22:44 CDT 2008


I need to put them back together again and store the entire 
address line in a new field.  This table has 97 million 
records so doing it "on-the-fly" whenever you want data just 
doesn't make sense.  Do it once, and be done with it.

I then send the addresses out to another program for 
validation.  That program expects a single address line.

John W. Colby
www.ColbyConsulting.com


Bob Gajewski wrote:
> 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