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 >