Too many characters in a multi-line field when line breaks stripped

[Ravensleach]

I have a command button that produces a text file, one string per record combining many fields. Here is the problem part of the code:

 If Not IsNull(rsHBData.Fields("Address")) Then
            strAddress = rsHBData.Fields("Address")
            strAddress = StripLineBreaks(strAddress, ", ", 0)
        Else
            strAddress = ""
        End If

The address string it produces strips out the line breaks in the table field and puts TWO commas between each line. I want it to produce just one comma and have tried many variations but it if the code doesn't break it always puts two in. Please, can anyone tell me what I should do?

Many thanks

 

[PHV]

What is the code of StripLineBreaks ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ravensleach]

Please see below. There is also in the module code to strip out commas (which exist in some of the original field at the end of address lines) in case you need to see that too. StripLineBreaks is in the second part

Function StripCommas(strS As String) As String
'Strips all commas from the specified string.
Dim i As Integer
Dim strTemp As String

    On Error GoTo Err_StripCommas
    glrEnterProcedure "StripCommas"

    strTemp = strS

    i = InStr(strTemp, ",")
    While i <> 0
        Mid$(strTemp, i, 1) = " "
        i = InStr(strTemp, ",")
    Wend

    StripCommas = strTemp

Exit_StripCommas:
    glrExitProcedure "StripCommas"
    Exit Function
Err_StripCommas:
    glrErrorOutput Err, Error$
    Resume Exit_StripCommas
End Function 'StripCommas

Line breaks

Function StripLineBreaks(strCurrentString As String, strDelim As String, iMaxLines As Integer) As String
On Error GoTo Err_StripLineBreaks
glrEnterProcedure "StripLineBreaks"
Dim strReplaced As String
Dim cchar As Integer
Dim intLen As Integer
Dim iLineCount As Integer
Dim i As Integer

    If IsNull(strCurrentString) Or strCurrentString = "" Then GoTo Exit_StripLineBreaks
    intLen = Len(strCurrentString)
    strReplaced = ""
    iLineCount = 0

    For i = 1 To intLen
        cchar = Asc(Mid$(strCurrentString, i, 1))
        Select Case cchar
        Case 13
            strReplaced = strReplaced & strDelim
            iLineCount = iLineCount + 1
'            If (iLineCount = iMaxLines) And (iMaxLines <> 0) Then Exit For
            If (iLineCount > iMaxLines) And (iMaxLines <> 0) Then Exit For
        Case 10
            ' skip
        Case 44 'comma
            strReplaced = strReplaced & strDelim
            iLineCount = iLineCount + 1
            If (iLineCount = iMaxLines) And (iMaxLines <> 0) Then Exit For
        Case Else
            strReplaced = strReplaced & Chr(cchar)
        End Select
    Next i

    ' Now strip extra commas off the end.
    i = intLen
    While (Mid$(strReplaced, i, 1) = ",") Or (Mid$(strReplaced, i, 1) = " ")
        i = i - 1
    Wend

    strReplaced = Left$(strReplaced, i)
    StripLineBreaks = strReplaced

Exit_StripLineBreaks:
    glrExitProcedure "StripLineBreaks"
    Exit Function

Err_StripLineBreaks:
    Select Case Err
    Case Else
        glrErrorOutput Err, Error$
    End Select
    Resume Exit_StripLineBreaks
End Function    'StripLineBreaks

 

[Ravensleach]

Help, anyone? I'm still struggling with this one ... many thanks

 

[PHV]

Please post some sample data with actual result vs expected result.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ravensleach]

Ok, not sure if this will attach the file but I'll try ... preview not working ...

 

[Ravensleach]

Ok that didn't work. Is there any way to send you a zip file?

Thanks

 

[PHV]

I don't want any zip file but some examples of Address with your actual result vs expected.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ravensleach]

Ok, thanks, here it is

THIS IS WHAT IT LOOKS LIKE NOW


1984 BEAR, Pooh, 410 Main Street, , Someplace, , Newtown, , Sheffie S10 4JP. Tel: 01284-56789; 09876-54321(Work).
1990 A BELL, Tinker, 1 High Street, , Anytown, , Somewhere, , Preston, , Lancash PR2 0EH.
1958 BROWN,A., 1 High Street, , Anytown, , Somewhere Els. Tel: 01284-56789.
1992 DE VILLE, Ms. Cruella, 1 High Street, , Anytown, , Somewher KA3 7BP. Tel: 01284-56789. E-Mail: cruella@btinternet.com
1985 DUCK, Donald, 1 High Street, , Anytown, , Somewher SW7 4ET. Tel: 01284-56789; 09876-54321(Work). (London Section Secretary 1985-1986. London Section Chairman 1986-1988)
1943 L HOOK, Capt., 10 Main Street, , Someplace, , Newtown, , Cumbr CA10 3HN. Tel: 01284-56789.
1969 H LIGHTYEAR, Buzz F., 6 Main Street, , Someplace, , Newtown, , Cumbr LA13 0LZ. Tel: 01284-56789. (Guidebook Writer: Dow Crag 1957)
1983 MOUSE, Micky, 10 Main Street, , Someplace, , Newtown, , New Zeala. Tel: 01284-56789. E-Mail: mickymouse@xtra.co.nz
1951 L MOUSE,Ms. Minnie Mary, 1 High Street, , Anytown, , Somewhere, , Gwyne LL54 7RF. Tel: 01284-56789.
1991 PAN, Peter, 710 Main Street, , Someplace, , Newtown, , Cheshi CW6 OHP.
1959 RABBIT, Mrs Jessica (nee Smith), 1 High Street, , Anytown, , Somewhere, , Surr KT23 4RZ. Tel: 01284-56789.
1970 RABBIT, Roger, 410 Main Street, , Someplace, , Newtown, , Oldham, , La OL2 7LJ. (Asst Journal Editor 1972-1976)
1955 L SMITH, A.H., 1 High Street, , Anytown, , Somewhere, , Cumbr LA22 9AU. (Asst. Librarian 1958-1960. Asst. Warden Birkness 1958-1961)


THIS IS WHAT IT SHOULD LOOK LIKE


1984 BEAR, Pooh, 410 Main Street, Someplace, Newtown, Sheffield S10 4JP. Tel: 01284-56789; 09876-54321(Work).
1990 A BELL, Tinker, 1 High Street, Anytown, Somewhere, Preston, Lancashire PR2 0EH.
1958 BROWN,A., 1 High Street, Anytown, Somewhere Else. Tel: 01284-56789.
1992 DE VILLE, Ms. Cruella, 1 High Street, Anytown, Somewhere KA3 7BP. Tel: 01284-56789. E-Mail: cruella@btinternet.com
1985 DUCK, Donald, 1 High Street, Anytown, Somewhere SW7 4ET. Tel: 01284-56789; 09876-54321(Work). (London Section Secretary 1985-1986. London Section Chairman 1986-1988)
1943 L HOOK, Capt., 10 Main Street, Someplace, Newtown, Cumbria CA10 3HN. Tel: 01284-56789.
1969 H LIGHTYEAR, Buzz F., 6 Main Street, Someplace, Newtown, Cumbria LA13 0LZ. Tel: 01284-56789. (Guidebook Writer: Dow Crag 1957)
1983 MOUSE, Micky, 10 Main Street, Someplace, Newtown, New Zealand. Tel: 01284-56789. E-Mail: mickymouse@xtra.co.nz
1951 L MOUSE,Ms. Minnie Mary, 1 High Street, Anytown, Somewhere, Gwynedd LL54 7RF. Tel: 01284-56789.
1991 PAN, Peter, 710 Main Street, Someplace, Newtown, Cheshire CW6 OHP.
1959 RABBIT, Mrs Jessica (nee Smith), 1 High Street, Anytown, Somewhere, Surrey KT23 4RZ. Tel: 01284-56789.
1970 RABBIT, Roger, 410 Main Street, Someplace, Newtown, Oldham, La OL2 7LJ. (Asst Journal Editor 1972-1976)
1955 L SMITH, A.H., 1 High Street, Anytown, Somewhere, Cumbria LA22 9AU. (Asst. Librarian 1958-1960. Asst. Warden Birkness 1958-1961)

BTW THE CODE WAS CREATED IN A VERY EARLY VERSION OF ACCESS, NEEDS DAO 3.6 Object Library to function

 

[PHV]

What about this ?

strAddress = Replace(rsHBData.Fields("Address"), ", ,", ",")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ravensleach]

Hi PHV

Tried it instead of the

strAddress = rsHBData.Fields("Address") line of code and as well as it, but still the same result - double commas

:-(

 

[TheAceMan1]

How are ya Ravensleach . . .

. . . and this:

[blue]Public Function Strip(strDat As String) As String
   
   strDat = Replace(strDat, ", ,", ",")    [green]'Strip commas[/green]
   Strip = Replace(strDat, vbNewLine, "") [green]'Strip Line Breaks[/green]
   
End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[Ravensleach]

Hi Aceman1

Thanks for your reply. It took out the extra commas, but it also took out the whole of the Address field too!! Weird because I can see the logic of your code ... tried both lines separately, but same effect.

Result:

1984 BEAR, Pooh, S10 4JP. Tel: 01284-56789; 09876-54321(Work).

 

[TheAceMan1]

[blue] . . . It took out the extra commas, but [purple]it also took out the whole of the Address field too!![/blue]

Thats something because I wound up with a single line of properly removed comma's & new line characters(as expected!).

Out of curiosity post the code as you have it . . .

See Ya! . . . . . .

Be sure to see thread181-473997