Partial Record Deletion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I hv following field & record

KPNum
--------
A1234
RF\3245
WEJ-123

Q: How to delete the alpha & character leaving only numbers?

TQ
 
hi,
KPNum
--------
A1234
RF\3245
WEJ-123
Q: How to delete the alpha & character leaving only numbers?
Click to expand...
Where do you get such bad data?

Aircode, place it in a standard module:

Public Function StripAlpha(AString As Variant) As Variant

Dim Count As Long
Dim Result As Variant
Dim Source As String

If IsNull(AString) Then
Result = Null
Else
Source = CStr(AString)
Result = ""
For Count = 1 To Len(Source)
Select Mid(Source, Count, 1)
Case 'A' To 'Z', 'a' To 'z'
Result = Result & Mid(Source, Count, 1)
End Select
Next Count
End If
StripAlpha = Result

End Sub

Use it in an update query: =StripAlpha([KPNum])


mfG
--> stefan <--
 
I've tried but got this "syntax error" on this line

Select Mid(Source, Count, 1)

TQ

Stefan Hoffmann said:
hi,
KPNum
--------
A1234
RF\3245
WEJ-123
Q: How to delete the alpha & character leaving only numbers?
Click to expand...
Where do you get such bad data?

Aircode, place it in a standard module:

Public Function StripAlpha(AString As Variant) As Variant

Dim Count As Long
Dim Result As Variant
Dim Source As String

If IsNull(AString) Then
Result = Null
Else
Source = CStr(AString)
Result = ""
For Count = 1 To Len(Source)
Select Mid(Source, Count, 1)
Case 'A' To 'Z', 'a' To 'z'
Result = Result & Mid(Source, Count, 1)
End Select
Next Count
End If
StripAlpha = Result

End Sub

Use it in an update query: =StripAlpha([KPNum])


mfG
--> stefan <--
Click to expand...
 
Hi,

Another syntax error on this line

Case 'A' To 'Z', 'a' To 'z'

Thanks for your response
 
hi,
Another syntax error on this line

Case 'A' To 'Z', 'a' To 'z'
Click to expand...
In VB(A) only double quotes are allowed as string delimiters. So use:
Case "A" To "Z"

btw, I forgot that string comparision is per default case insensitive.
So you don't need the "a" To "z" part.


mfG
--> stefan <--
 
Back
Top