Replacing Spaces with "-" 1

[GoingCrazy]

I know you all have come close to answering this in thread181-96793 but something isn't working quite right...

I was given a document with a field of part numbers that have spaces inserted instead of "-". I need to add the dashes back in. Unfortunately, the part numbers are of varying lengths and the dashes go in different places. For example,

12 345676 2
123456789 234
1 23456

I tried using the response randy@marconet.net provided in the above thread, and replace the "" with "-", but I can't get the query to recognize the public function I placed in a new module. Can anyone provide me with a simple solution or tell me what I am doing wrong.

As always, any help would be greatly appreciated.

 

[tlbroadbent]

Try this function.

Public Function ReplaceSpaces(sInput As String) As String
Dim strRet As String, strChar As String
Dim intI As Integer, intC As Integer

intI = Len(sInput)
For intC = 1 To intI
strChar = Mid(sInput, intC, 1)
If strChar = " " Then
strRet = StrRet & "-"
Else
strRet = strRet & strChar
End If
Next

ReplaceSpaces = strRet
End Function
---------------------------

Use the function in an Update query.

Update table set column = ReplaceSpaces(column)
Where column Like "* *" Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[GoingCrazy]

Thanks Terry. I'll give it a try. I appreciate the help.

 

[GoingCrazy]

OK, I think I have everything in the right place but...
The function is in a new module named "ReplaceSpaces".I built an update query and put the "=ReplaceSpaces(column)" in the update to box and in the criteria box I put Like "* *". When I run the query the result is: "ReplaceSpaces is an undefined function." Am I forgetting to do something or do I have something in the wrong spot.

Thanks...

 

[tlbroadbent]

Test the module in the debug window. Open the module. Then open the debug window. In the debug Window type the following.

?ReplaceSpaces("This should not have spaces.&quot

When you hit Enter the routine should return...

Thisshouldnothavespaces.

Let me know what happens. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[GoingCrazy]

Terry,

I just figured it all out. I had to insert the module instead of just open a new one. I was also using (column) instead of the field name. It's working like a champ. Now I am comparing the new list of part numbers with the dashes to another list. Since nothing is matching and I can visually see matches between the two lists, it begs the question - are the dashes visible in the query?

Thanks for your help.

 

[GoingCrazy]

Never mind. I guess I'm just not back in the groove from the long weekend. I just figured it out too.

Thanks again for your help.