[AccessD] Put humpty dumpty back together again

    Prima-facie, the solution suggested by JB (placed below) should meet the requirement. It is straightforward, and does not impose any function calls. It is not clear whether JC has responded to this.

    Note: It seems that at some places, intended + sign has been replaced by = sign during email processing.

A.D.Tejpal
------------


----- Original Message ----- 
From: John Bartow 
To: 'Access Developers discussion and problem solving' 
Sent: Friday, June 06, 2008 20:34
Subject: Re: [AccessD] Put humpty dumpty back together again


I'm assuming your using Access queries to do this:

SELECT HouseNumberPrefix, HouseNumber, HouseNumberSuffix, Direction,
StreetName, Mode , Quadrant, Appt# ([HouseNumberPrefix]+" ") &
([HouseNumber]+" ") & ([HouseNumberSuffix]=" ") & ([Direction]=" ") &
([StreetName]=" ") & ([Mode ]=" ") & ([Quadrant]=" ") & [Appt#] AS
FullAddress
FROM tblPerson

HTH
John B.

  ----- Original Message ----- 
  From: jwcolby 
  To: Access Developers discussion and problem solving ; Dba-Sqlserver 
  Sent: Friday, June 06, 2008 19:50
  Subject: [AccessD] Put humpty dumpty back together again


  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