remove unwanted characters

[Trudye]

Hi Guru's

How can I remove unwanted characters from a field. I know that TRIM (left/right) will remove spaces. Is there a function to remove unwanted characters wheather it be from front, back or middle?


Thanks
Trudye

 

[PaulBricker]

Well you could try something like this. You could call it from a query and enter the FieldName for the strValue arugment.

Function cleanString(strValue as String)
Dim i as Integer
Dim strHolder as String

For i = 1 To Len(strValue)
If Mid(strValue,i,1)<> &quot; &quot; 'for a space or Chr(34) for &quot; or whatever value you don't want
strHolder = strHolder & Mid(strValue,i,1)
Else
End If
Next

cleanString = strHolder

Paul

 

[PaulBricker]

Sorry, forgot the Then in the If..Then line. You will need to add that.

Paul

 

[MichaelRed]

or just look up the replace function.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Trudye]

Thanks Paul for the feedback. I took your function and enhanced it to suite my purpose.

cleanString:
strHolder = &quot; &quot;
s_Hrs = Len(strValue)

If s_Hrs > 11 Or s_Hrs < 10 Then
MsgBox &quot;problem with Expected Care calculation, contact Administrator&quot;, vbOKOnly
Return
End If

If s_Hrs = 11 Then
s_Hrs = 5
End If

If s_Hrs = 10 Then
s_Hrs = 4
End If
For i = 1 To s_Hrs
strHolder = strHolder & Mid(strValue, i, 1)
Next
cleanedString = strHolder
Return

Thanks so much
Trudye

 

[Trudye]

Hi Mike:

I thought about the Replace function but with Replace I had to define the values I wanted to get remove. That was a little messier than I wanted to get, I was looking for quick and dirty.

As always thanks for the input
Be well
Trudye