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