Replacing Spaces in a field 3

[eojhan]

Greetings,

I have 2 questions.

I would like to create a module that will replace spaces in a field in one of my tables. I tried a function I found on here but it did not work the way I expected. (It deleted a lot of the values in the field completely.)

The second part of the question is that halfway through the process, I recieved an message telling me there was not enough memroy for an undo operation. Is there a way to override that message? (I want to be able to kick this module off from a macro called from a bat file started by the Scheduling service.)

TIA,

Owen

 

[TrojanRabbit]

The following might work for replacing spaces:

i = InStr(1, str, " &quot
While i
str = Left(str, i-1) & <replacement string> & Mid(str,i+1)
i = InStr(1, str, &quot; &quot
Wend

Warnings can be turned off with the following:

DoCmd.SetWarnings False

Whenever you use this, you need to remember to turn warnings back on if needed (DoCmd.SetWarnings True).

 

[HenryAnthony]

Thanks to Ben O'Hara and the code author Alden Streeter I am able to forward this code to you. It works for me.

To answer the second part of your question, I am not sure exactly how, but I am pretty sure you can turn off warnings in macros as well as VBA. Try typing &quot;warnings&quot; in help.

For the first part of your question here is the code you need. There are certainly other ways to approach this problem but this one works for me. Look back a few messages to find the thread. There are some other useful suggestions from other forum participants on this topic.

Put this code into a module:

'************ Code Start **********
'This code was originally written by Alden Streeter.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Alden Streeter
'
Function FindAndReplace(ByVal strInString As String, _
        strFindString As String, _
        strReplaceString As String) As String
Dim intPtr As Integer
    If Len(strFindString) > 0 Then
        Do
            intPtr = InStr(strInString, strFindString)
            If intPtr > 0 Then
                FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
                                        strReplaceString
                    strInString = Mid(strInString, intPtr + Len(strFindString))
            End If
        Loop While intPtr > 0
    End If
    FindAndReplace = FindAndReplace & strInString
End Function
'************ Code End **********

then create an update query on the table you are manipulating:

UPDATE tblData SET tblData.Surname = findandreplace([surname],&quot;'&quot;,&quot;*&quot;

change tblData & surname to match your data.