?
Solved

Extra spaces in a record

Posted on 2005-04-11
8
Medium Priority
?
239 Views
Last Modified: 2012-05-05

 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
0
Comment
Question by:maralans
  • 4
  • 3
8 Comments
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 13755116
This is because some of your fields are empty.  
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 13755201
Is this Access or SQL Server?

Bob
0
 
LVL 5

Author Comment

by:maralans
ID: 13755210
It is Access
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 13755226
Example:
sHseNum & ' ' & IIF(IsNull(sHseFrac), '', sHseFrac &' ') & sPfx

Bob
0
 
LVL 5

Author Comment

by:maralans
ID: 13755340
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
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 13755376
          "sHseNum&' '& IIF(IsNull(sHseFrac),'',sHseFrac&' ') & sPfx&' '&sStreet&' '&sSfx AS sFullAddress," & _

Bob
 
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 13755391
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
 
LVL 5

Author Comment

by:maralans
ID: 13755476
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question