I am using VB 6.0 and need to remove hyphens and replace them with asterisks from a string variable; then remove all spaces from the string and insert an asterisk at the beginning and end of the string. I need to do this so I can use it in a SQL statement.

I have written a function which passes in the string but it does not work properly. Can someone have a look at it and tell me what I am missing here please. The documentation in the code should explain the probem I have having.

Public Function FormatDrawingNumber(DrawingNum)
Dim X, Y, Z, iLength As Integer

DrawingNum = Trim(DrawingNum)
iLength = Len(DrawingNum)

'At this point DrawingNum = "1246-D0- 238 -"
'and its length is 14

'This is designed to remove the hyphens
'and replace them with asterisks

For X = 1 To iLength

If InStr(1, Trim(DrawingNum), "-", 1) Then
Y = InStr(X, Trim(DrawingNum), "-", 1)
DrawingNum = Left(DrawingNum, Y - 1) & "*" & Right(DrawingNum, iLength - Y)
End If

Next X

iLength = Len(DrawingNum)

'The above code worked because
'now the DrawingNum = "1246*D0* 238 *"
'and its length is still 14

'The following code is designed to remove any spaces
'found within the string

For Z = 1 To iLength

If InStr(1, Trim(DrawingNum), " ", 1) Then
Y = InStr(Z, Trim(DrawingNum), " ", 1)
If Y <> 0 Then
DrawingNum = Left(DrawingNum, Y - 1) & Right(DrawingNum, iLength - Y)
End If
End If

Next Z

iLength = Len(DrawingNum)

'DrawingNum = "1246*D0*238 *" and its length is now 13
'The first space between has been removed but it did
'not remove the last space which is just before the last asterisk

'This will add an asterisk to the beginning of the string
DrawingNum = "*" & DrawingNum

End Function