Create a New Field in Query by Extracting part of another field

[Tmat]

I have this one field that contains data like this

Field 1 what I need
DE DE
CTT111 CTT
CY22 CY
BRBB BRBB
WHCH201 WHCH

What I am trying to do is determine if there are any numbers in the field if so then extract the letters to the left of the number if there is no number then extract the whole field.

I have been trying this with the expression builder using IIf and String and Search and left functions nesting them but nothing I have put together seems to work.
Any help would be appreciated

 

[rhicks]

Place the following function in a module:

Public Function CheckEntry(strFld As Variant) As String
Dim strTemp As String
Dim x As Integer
If IsNull(strFld) Then Exit Function
For x = 1 To Len(strFld)
If Not IsNumeric(Mid$(strFld, x, 1)) Then
strTemp = strTemp & Mid$(strFld, x, 1)
Else
Exit For
End If
Next x
CheckEntry = strTemp
End Function

Now use this function in an Expression in your Query:

ExpName:CheckEntry([Field 1])

This should do what you want.

HTH :-Q
RDH


Ricky Hicks
rdhicks@mindspring.com

 

[Tmat]

That worked perfect.Thanks for the help.