Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Searching thorugh a string and replacing found characters with others: Chr(13) and Chr(10) combined 2

Status
Not open for further replies.
Triacona

Triacona

Technical User
Jun 11, 2009
462
GB
Dear All,

Thanks for a brilliant forum! [smile]
I have a function ReplaceX.
It is meant to find Chr(13) and substitute it with a ", " (comma space)
This worked until our suppliers now changed the way the address are formatted.
They now stipulate that there is a Space and a carriage return after each line of address.
So therefore a Chr(10) and Chr(13).

Below is my VB function to sort through the string and replace Chr(13).

Code: 
[COLOR=#204A87]Option Compare Binary[/color]
[COLOR=#4E9A06]' Otherwise, the function will replace spaces with percent signs.[/color]
[COLOR=#204A87]Option Explicit[/color]
                    
[COLOR=#4E9A06]'============================================================
'The following function will:
' - Find the tabs in a Text or Memo field.
' - Call another function to replace the tabs.
'============================================================[/color]

[COLOR=#204A87]Function[/color] FindTabs(WhichField [COLOR=#204A87]As String[/color]) [COLOR=#204A87]As String[/color]
[COLOR=#204A87]Dim[/color] x [COLOR=#204A87]As Integer[/color], strText [COLOR=#204A87]As String[/color]
[COLOR=#204A87]Dim[/color] start [COLOR=#204A87]As Integer[/color]
[indent]start = 1
x = 1
strText = WhichField[/indent]

[COLOR=#204A87]Do Until[/color] x = 0
[COLOR=#4E9A06]' Chr(13) is the carriage return character.
' Replace Chr(9) with the ANSI code for the character
' you are searching for.[/color]
[indent]x = InStr(start, strText, vbCrLf)   [COLOR=#4E9A06]'vbCrLf replacing Chr(13), because now our database adds a space then return, not just a return[/color][/indent]               
[indent]start = x + 1[/indent]
[COLOR=#204A87]If[/color] x > 0 [COLOR=#204A87]And Not[/color] IsNull(x) [COLOR=#204A87]Then[/color]                  
[indent]strText = ReplaceTabs(x, strText)[/indent]
[COLOR=#204A87]End If
Loop[/color]
[indent]FindTabs = strText[/indent]
[COLOR=#204A87]End Function[/color]

[COLOR=#4E9A06]'==================================================================
' The following function is called from the FindTabs() function. It
' accepts two arguments, strText and start. The function replaces
' tabs with %. It returns the updated text.
'==================================================================
[/color]

[COLOR=#204A87]Function[/color] ReplaceTabs(start [COLOR=#204A87]As Integer[/color], strText [COLOR=#204A87]As String[/color]) [COLOR=#204A87]As String[/color]
[COLOR=#4E9A06]' Replace % with the character you want to substitute.[/color]
[indent]Mid(strText, start, 1) = ", "
ReplaceTabs = strText[/indent][COLOR=#204A87]End Function[/color]
                    
[COLOR=#204A87]Function[/color] OneLineReplace(strText As String) [COLOR=#204A87]As String[/color]         
[COLOR=#4E9A06]'Replace % with the character you want to substitute.[/color]
[indent]OneLineReplace = Replace(strText, vbCrLf, ", ")   [COLOR=#4E9A06]'vbCrLf replacing Chr(13), because now our database adds a space then return, not just a return[/color][/indent]
[COLOR=#204A87]End Function[/color]

So now instead of trying just to replace Chr(13) I have to find and replace Chr(13) and Chr(10) simultaneously with ", " within my function, so I can use it within my query -
I.e. SiteAddress: OneLineReplace([UNI7LIVE_DCAPPL].[ADDRESS])

Any help will be very, very much appreciated, I am desparate, I have tried quite a few ways, but none seem to work. I desparatly need this, as alot of my reports are letters/certificates sent out.

Thank you !![bigsmile]

Thank you,

Kind regards

Triacona
 
Sort by date Sort by votes
So what happens when you use what you have now:
[tt]
Replace(strText, vbCrLf, ", ")
[/tt]
?

vbCrLf is Chr(13) and Chr(10), or Chr(10) and Chr(13), don't remember the order
You may also try [tt]vbNewLine[/tt]

Have fun.

---- Andy
 
Upvote 0 Downvote
Dear Andy,
Thank you very much for your help! [smile]
When I run replace vbCrlf I get the following error:
Data type mismatch in criteria expression.
Click to expand...
I will give vbNewLine a try. Thanks! [smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Dear Andy,
I tried vbNewLine and get the same error as above:
Data type mismatch in criteria expression.
Click to expand...

The criteria is in my query:
SQL below:
Code: 
SELECT UNI7LIVE_DCAPPL.REFVAL, UNI7LIVE_DCAPPL.OFFCODE, UNI7LIVE_DCAPPL.DCAPPTYP, UNI7LIVE_DCAPPL.DCSTAT, 
OneLineReplace([UNI7LIVE_DCAPPL].[ADDRESS]) AS [Site Address], Count(UNI7LIVE_DCAPPL.REFVAL) AS [Count], UNI7LIVE_DCAPPL.DATEAPVAL
FROM UNI7LIVE_DCAPPL
GROUP BY UNI7LIVE_DCAPPL.REFVAL, UNI7LIVE_DCAPPL.OFFCODE, UNI7LIVE_DCAPPL.DCAPPTYP, UNI7LIVE_DCAPPL.DCSTAT, 
OneLineReplace([UNI7LIVE_DCAPPL].[ADDRESS]), UNI7LIVE_DCAPPL.DATEAPVAL;
As you can see the OneLineReplace function is used for the address field.
Any further help would be awesome thanks![smile]


Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
It looks like it is not a text in your field:[tt]
[UNI7LIVE_DCAPPL].[ADDRESS][/tt]

Could you show here an example of what you have in this field?

Is it possibile you may have NULL in there? If so, use NZ()

You can just try this to see if you have problem with vbCrLf and ", " or is it something else:

Code: 
Function OneLineReplace(strText As String) As String
OneLineReplace = Replace(strText, "ABCD", "XYZ")   
End Function

Have fun.

---- Andy
 
Upvote 0 Downvote
Dear Andy,
If I run the query without OneLineReplace and copy the text within the ADDRESS field then it pastes as such:
Pemberton
Church Road
Woking
Horsell
Surrey
GU21 4QY
But within the query, it looks as such:
PembertonChurch RoadWokingHorsellSurreyGU21 4QY
Note after the postcode there are seven spaces.
How would I use NZ()?
How would I use vbCrLf and ", " simultaneously in both replace and instr?

Thank you again for all your help, it is much appreciated [bigsmile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote

Is it possibile you may have NULL in there?
You can check by running this:
[tt]
SELECT *
FROM UNI7LIVE_DCAPPL
WHERE [UNI7LIVE_DCAPPL].[ADDRESS] IS NULL
[/tt]
If you get any records, you have some NULLs in ADDRESS field.

Have fun.

---- Andy
 
Upvote 0 Downvote
>here is a Space and a carriage return after each line of address.
>So therefore a Chr(10) and Chr(13).


Just one quick point: Chr(10) is not a space. Chr(32) is ...

Chr(10) is LF
Chr(13) is CR

Thus vbCRLF (and vbNewLine) is Chr(13) & Chr(10)

So, your Function, given your explanation of what you want to do (and the comment in the function itself), is wrong (before we start worrying about NULLS and so on)

Code: 
[blue]Function OneLineReplace(strText As String) As String         
    [green]'Replace % with the character you want to substitute.[/green]
    OneLineReplace = Replace(strText, " " & vbCR, ", ")   [green]' because now our database adds a space then return, not just a return[/green]
End Function[/blue]
 
Upvote 0 Downvote
Hi All,
Thank you for all your help [bigsmile]
I have tried Strongm's suggestion it still gives the error, (but it has a NULL value)
Strongm in regards to explaining the function I forgot to update my comment within the code it has to replace chr(13) first then chr(10).
Before the DB, new version of Oracle, was upgraded my Function worked perfectly.
It is just the addition of the Chr(10) at the end (Line Feed) that has now caused a problem.

I have checked for NULL and yes there was a NULL value [smile] Well done! [thumbsup]
So I now have the code below.
Is there a way to build in compatibilty for NULL values, so my report does not fall over everytime someone forgets to enter an address??

Thank you ! [bigsmile]



Code: 
[COLOR=#204A87]Option Compare Binary[/color]
[COLOR=#4E9A06]' Otherwise, the function will replace spaces with percent signs.[/color]
[COLOR=#204A87]Option Explicit[/color]
                    
[COLOR=#4E9A06]      '============================================================
      'The following function will:
      ' - Find the tabs in a Text or Memo field.
      ' - Call another function to replace the tabs.
      '============================================================[/color]
      [COLOR=#204A87]Function[/color] FindTabs(WhichField [COLOR=#204A87]As String) As String[/color]
         [COLOR=#204A87]Dim[/color] x [COLOR=#204A87]As Integer[/color], strText [COLOR=#204A87]As String[/color]
         [COLOR=#204A87]Dim[/color] start [COLOR=#204A87]As Integer[/color]
            start = 1
            x = 1
            strText = WhichField

            [COLOR=#204A87]Do Until[/color] x = 0
               [COLOR=#4E9A06]' Chr(13) is the carriage return character.
               ' Replace Chr(9) with the ANSI code for the character
               ' you are searching for.[/color]
               x = InStr(start, strText, Chr(13)) [COLOR=#4E9A06]'vbCrLf replacing Chr(13), because now our database adds a return then LF, not just a return[/color]
               start = x + 1
               [COLOR=#204A87]If[/color] x > 0 [COLOR=#204A87]And Not[/color] IsNull(x) [COLOR=#204A87]Then[/color]
                  strText = ReplaceTabs(x, strText)
               [COLOR=#204A87]End If[/color]
            [COLOR=#204A87]Loop[/color]
            FindTabs = strText
      [COLOR=#204A87]End Function[/color]

     [COLOR=#4E9A06] '==================================================================
      ' The following function is called from the FindTabs() function. It
      ' accepts two arguments, strText and start. The function replaces
      ' tabs with %. It returns the updated text.
      '==================================================================[/color]

      [COLOR=#204A87]Function[/color] ReplaceTabs(start [COLOR=#204A87]As Integer[/color], strText [COLOR=#204A87]As String[/color]) [COLOR=#204A87]As String[/color]
         [COLOR=#4E9A06]' Replace % with the character you want to substitute.[/color]
         Mid(strText, start, 1) = ", "
         ReplaceTabs = strText
      [COLOR=#204A87]End Function[/color]
                    
     [COLOR=#204A87]Function[/color] OneLineReplace(strText As String) [COLOR=#204A87]As String[/color]
         [COLOR=#4E9A06]' Replace % with the character you want to substitute.[/color]
         OneLineReplace = Replace(strText, Chr(13), ", ")   [COLOR=#4E9A06]'vbCrLf replacing Chr(13), because now our database adds a chr(13) first then chr(10), not just a chr(13)[/color]   
      [COLOR=#204A87]End Function[/color]


Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Oh by the way the code above works, so long there is no NULLS [smile]
Thanks [smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
so long there is no NULLS
Whic function fails when there is a null value ?
My suggestion:
Code: 
Function OneLineReplace(strText As Variant) As String
If Trim(strText & "") = "" Then
    OneLineReplace = ""
Else
    OneLineReplace = Replace(strText, Chr(13), ", ")
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Dear PHV,

Thanks for your help [smile]
Yes logically your additive to my function should work when there is a null address.
But unfortunately it does not.
Unfortunately when I get the error message, it does not divulge which function is bombing it out.
I even tried the if statement you provided in the Function Findtabs, in which strText is declared as a string.

Code: 
[COLOR=#204A87]Option Compare Binary[/color]
         [COLOR=#4E9A06]' Otherwise, the function will replace spaces with percent signs.[/color]
[COLOR=#204A87]Option Explicit[/color]
                    
    [COLOR=#4E9A06]'============================================================
    'The following function will:
    ' - Find the tabs in a Text or Memo field.
    ' - Call another function to replace the tabs.
    '============================================================[/color]
[COLOR=#204A87]Function[/color] FindTabs(WhichField As String) [COLOR=#204A87]As String[/color]
    [COLOR=#204A87]Dim[/color] x [COLOR=#204A87]As Integer[/color]
        x = 1
    [COLOR=#204A87]Dim[/color] strText [COLOR=#204A87]As String[/color]
        strText = WhichField
    [COLOR=#204A87]Dim[/color] start [COLOR=#204A87]As Integer[/color]
        start = 1
          
        [COLOR=#204A87]Do Until[/color] x = 0
        [COLOR=#4E9A06]' Chr(13) is the carriage return character.
        ' Replace Chr(9) with the ANSI code for the character
        ' you are searching for.[/color]
            x = InStr(start, strText, Chr(13))
            start = x + 1
            [COLOR=#204A87]If[/color] x > 0 [COLOR=#204A87]And Not[/color] IsNull(x) [COLOR=#204A87]Then[/color]
                strText = ReplaceTabs(x, strText)
            [COLOR=#204A87]End If[/color]
        [COLOR=#204A87]Loop[/color]
            FindTabs = strText
    
[COLOR=#204A87]End Function[/color]
      
      

[COLOR=#4E9A06]      '==================================================================
      ' The following function is called from the FindTabs() function. It
      ' accepts two arguments, strText and start. The function replaces
      ' tabs with %. It returns the updated text.
      '==================================================================[/color]

[COLOR=#204A87]Function[/color] ReplaceTabs(start [COLOR=#204A87]As Integer[/color], strText [COLOR=#204A87]As String[/color]) [COLOR=#204A87]As String[/color]
[COLOR=#204A87]' Replace % with the character you want to substitute.[/color]
    Mid(strText, start, 1) = ", "
        ReplaceTabs = strText
[COLOR=#204A87]End Function[/color]



                    
[COLOR=#204A87]Function[/color] OneLineReplace(strText As String) [COLOR=#204A87]As String[/color]' Replace % with the character you want to substitute.
    [COLOR=#204A87]If[/color] Trim(strText & "") = "" [COLOR=#204A87]Then[/color]
        OneLineReplace = ""
    [COLOR=#204A87]Else[/color]
        OneLineReplace = Replace(strText, Chr(13), ", ")
    [COLOR=#204A87]End If
End Function[/color]

[COLOR=#4E9A06]'chr(13) first then chr(10)[/color]
So this is used in a query, so the address is passed through the function.
I would have thought where I initially declared the variables that this would be the point of using the if isnull or trim("") = "",
but this does not seem to be the case??
Maybe I am reading my code wrong or I doing something else wrong...
Any further help would be greatly appreciated.

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
A string variable can't hold Null value.
Seems you didn't notice how I defined the strText parameter in my suggestion ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Ah, sorry I see you decalered it as a variant as as string, didn't know you could do that, will try now [smile]
Thanks [smile]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
You are a genuis! [2thumbsup]!!!
The variant works, phew!
Thank you ever so much and for noticing that I made a mistake, hehe [wink]
Why do we fall, so we can learn to get up!
Thanks, it all works, nulls included.
So within a function, one can declare a variable in 2 different ways??
Is there somewhere I could read up on this as I'm a noob?

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
Have you tried my suggestion of replacing NULL with some other value, like "", when your function is called? Did you look up NZ() function?

You wold have something like:
[tt]
Select ABC, OneLineReplace([red]NZ([/red][blue]SomePossibleNullField[/blue],[red] "")[/red]), XYZ, ...
[/tt]



Have fun.

---- Andy
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Question
Open filtered for and set cursor at particular record
Replies
1
Views
124
Moss100
Moss100
M
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
382
SoftwareRT
SoftwareRT
Moss100
  • Locked
  • Question
Remove everything including the comma from the last comma of a string. 1
Replies
10
Views
341
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Open New Access Database and Maximise the Application Window
Replies
0
Views
227
Moss100
Moss100
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
191
Dan Stoica
Dan Stoica

Part and Inventory Search

Sponsor

Back
Top