Replace ~ with new line

[drschlong]

Hi all,

Been a long time since I posted anything on here so hope you don't mind helping me out.

I have an address field on a report which looks something like this

1 Example Street~Example Village~Example Town~Example County etc.

What I need to do is everytime the ~ symbol appears I need to replace it with a new line. I don't want to convert all the existing data in the tables as the data comes from a system I have no control over(just have access to the tables for reporting purposes etc.), and so am unable to make changes to the way data is held in the tables, I just need to do it to this one field on the report.

Hope somebody can help.

Thanks Steven.

p.s. I am using Access 97.

 

[maxmangion]

Me.AddressField = Replace(Me.AddressField, "~", vbcrlf)

Replace AddressField with the proper name of your field.

 

[drschlong]

maxmangion, thanks for your reply, but when I run the report it does not seem to recognise the Replace function you used. Any ideas?

 

[maxmangion]

why don't you do it at form level on an unbound text box, and then put this newly created text box in your report.

 

[drschlong]

I am doing it at form level. Could it be you are using a different version of access which has this replace function built in. I keep getting the message:-

Compile error: Sub or Function not defined

and then the word Replace is highlighted in yellow.

 

[maxmangion]

actually, i am using Access 2000, so i cannot confirm if the Replace function is available in 97 or not. Maybe if you do a search on google, you will see if it exists in 97, or if there is an alternative to it.

Goodluck.

 

[john471]

I don't believe "Replace" is present in AC97.

Here is a function i wrote to perform the equivalent...

Option Compare Database
Option Explicit

Public Function szReplaceAll(ByVal szInput As String, szFind As String, szReplaceWith As String) As String
    'Replace each (and every) occurrence of szFind in szInput with an instance of szReplaceWith
    
    'Version 1.1  10 JUN 2004
    'amended to work with multiple characters/multi length strings
    
    Dim lngPos As Long
    Dim nLenFind As Integer
    Dim szLeft As String
    Dim szRight As String
    
    nLenFind = Len(szFind)
    
    lngPos = InStr(1, szInput, szFind, vbTextCompare)
    
    'mid(x,y,z)= "" Doesn't seem to work properly, so manually construct the same result !
    If szReplaceWith = vbNullString Then
        Do While lngPos > 0
            
            'If lngPos = 1 Then 'first character is "offensive"
            '    szInput = Right$(szInput, Len(szInput) - nLenFind)
            'Else
                szInput = Left$(szInput, lngPos - 1) & Right$(szInput, Len(szInput) - lngPos - nLenFind + 1)
            'End If
            lngPos = InStr(1, szInput, szFind, vbTextCompare)
        Loop
    Else
        Do While lngPos > 0
            'only works with single characters
            'or where find and replace are same length
            'Mid(szInput, lngPos, Len(szFind)) = szReplaceWith
            'the below works better...
            szLeft = Left$(szInput, lngPos - 1)
            szRight = Right$(szInput, Len(szInput) - lngPos - nLenFind + 1)
            szInput = szLeft & szReplaceWith & szRight
            lngPos = InStr(1, szInput, szFind, vbTextCompare)
        Loop
    End If
    
    szReplaceAll = szInput
End Function

debug.Print szreplaceall ("1 Example Street~Example Village~Example Town~Example County","~",vbcrlf)

would return :-

1 Example Street
Example Village
Example Town
Example County

HTH

Regards

John.

 

[drschlong]

John, thanks alot, got that to work a treat. Also thank you to Maxmangion for trying to help me earlier.