Partial Record Deletion

[zyus]

I hv following field & record

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

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

TQ

 

[Stefan Hoffmann]

hi,

KPNum
--------
A1234
RF\3245
WEJ-123
Q: How to delete the alpha & character leaving only numbers?

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 <--

 

[zyus]

I've tried but got this "syntax error" on this line

Select Mid(Source, Count, 1)

TQ

hi,

KPNum
--------
A1234
RF\3245
WEJ-123
Q: How to delete the alpha & character leaving only numbers?

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 <--

 

[Stefan Hoffmann]

hi,

I've tried but got this "syntax error" on this line
Select Mid(Source, Count, 1)

Yup, typo. Must be: Select Case Mid()


mfG
--> stefan <--

 

[zyus]

Hi,

Another syntax error on this line

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

Thanks for your response

 

[Stefan Hoffmann]

hi,

Another syntax error on this line

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

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 <--