Extra spaces in a record


 Hi,
I am trying to merge some fields into a complete address but I am getting extra spaces within the address.  Is there a way I can fix this?  Thanks for your help.

 With Cmd
            .Connection = conn
            .CommandText = ("SELECT sRemarks, sAcctNum, sFname&' '&sLname AS sFullName, sPhone," & _
            "sHseNum&' '&sHseFrac&' '& sPfx&' '&sStreet&' '&sSfx AS sFullAddress," & _                                             <--Part that merges the fields into Full Address
            "sAptNum, sCity, sState, sZip, sPubId, sFodID, sCopies, sAtz," & _
            "sActive, sActiveID, sReason, sInst, sMsg, sMailDate, sRouteNum,sSun," & _
            "sMon, sTue, sWed, sThu, sFri, sSat  INTO Subscribers IN" & _
            "'c:\program files\nads\nadsdatabase.mdb' FROM [TempAsl];")
            On Error Resume Next
            .ExecuteNonQuery()
        End With

871   SCHALLERT ST  <--3 spaces between the 1 and S
126  S MARCELLA AVE  <--2 spaces between the 6 and S
LVL 5
maralansAsked:
Who is Participating?
 
Bob LearnedCommented:
          "sHseNum&' '& IIF(IsNull(sHseFrac),'',sHseFrac&' ') & sPfx&' '&sStreet&' '&sSfx AS sFullAddress," & _

Bob
 
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
This is because some of your fields are empty.  
0
 
Bob LearnedCommented:
Is this Access or SQL Server?

Bob
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
maralansAuthor Commented:
It is Access
0
 
Bob LearnedCommented:
Example:
sHseNum & ' ' & IIF(IsNull(sHseFrac), '', sHseFrac &' ') & sPfx

Bob
0
 
maralansAuthor Commented:
Bob,

I put that in to see if it would work and it won't create the table.  I am doing something wrong.

  With Cmd
            .Connection = conn
            .CommandText = ("SELECT sRemarks, sAcctNum, sFname&' '&sLname AS sFullName, sPhone," & _
            "sHseNum&' '& IIF(IsNull(sHseFrac),'',sHseFrac&' '& sPfx&' '&sStreet&' '&sSfx AS sFullAddress," & _
            "sAptNum, sCity, sState, sZip, sPubId, sFodID, sCopies, sAtz," & _
            "sActive, sActiveID, sReason, sInst, sMsg, sMailDate, sRouteNum,sSun," & _
            "sMon, sTue, sWed, sThu, sFri, sSat  INTO Subscribers IN" & _
            "'c:\program files\nads\nadsdatabase.mdb' FROM [TempAsl];")
            On Error Resume Next
            .ExecuteNonQuery()
        End With
0
 
Bob LearnedCommented:
This hides any errors:

On Error Resume Next

Is it totally necessary?  IMHO it is not good to obscure errors, since it will be harder to pinpoint where something goes wrong when you can't even tell that an error occurred, especially when an application grows larger.

Bob
0
 
maralansAuthor Commented:
Bob,

Okay there it worked.  I can finish the code now.  I know my error stuff needs addressed.  I picked that up somewhere else on here but I am really new at this and just moving forward as I can.  Thanks alot for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.