Alpha chrs from a field

[harrisw]

Is there a way that from a postcode field I can just extract the letters only from the first part of the postcode?

ie bb11 = bb

a12 = a

etcetc

I need to be able to do this from a query.

 

[raskew]

Try this. It's designed to delete either numeric or non-numeric characters from a string, as specified by the user. You can call it from a calculated field of a query just like most other functions.

Function scrubomatic(ByVal thestuff As String, KeepNum As Boolean) As Variant
'*******************************************
'Name:      scrubomatic (Function)
'Purpose:   Remove all numeric or non-numeric
'           characters from a string
'Inputs:    from debug window:
'   (1) To remove non-numeric characters  ? scrubomatic("abc$123", True)
'   (2) To remove numeric characters    ? scrubomatic("abc$123 xyz456", False)
'Output:
'   (1) 123
'   (2) abc$
'*******************************************

Dim strHold As String, intLen As Integer, n As Integer
strHold = RTrim(thestuff)
intLen = Len(strHold)
strHold = ""
For n = 1 To intLen
   If KeepNum = False Then  'Remove numeric characters
      strHold = strHold & IIf(Not IsNumeric(Mid(thestuff, n, 1)), Mid(thestuff, n, 1), "")
   Else                     'retain only numeric characters
      strHold = strHold & IIf(IsNumeric(Mid(thestuff, n, 1)), Mid(thestuff, n, 1), "")
   End If
Next n
scrubomatic = strHold
End Function