Alphabets in a field

[rajeevs]

Hi

Previously I asked this question and I got the answer from the moderator.
I am getting all the alphabets, but now i want to get the alphabets before the first occurance of numeric value. I am posting the previous procedure here.
If i run through the example like,

abcd123gh
The answer is abcdgh.
But I want to get the result as abcd. Please help me. Thank you for the support always provided by you all.

My earlier post title was Alphabets in a field and the procedure given by Jking
The procedure was

Private Sub cmdRun_Click()
Dim OldString As String
Dim NewString As String
Dim rstAlpha As Recordset
Dim rstAlphaNum As Recordset
Dim I As Integer

Set rstAlpha = CurrentDb.OpenRecordset("tblAlpha", dbOpenDynaset)
Set rstAlphaNum = CurrentDb.OpenRecordset("tblAlphaNum", dbOpenDynaset)

rstAlphaNum.MoveFirst

Do While rstAlphaNum.EOF = False
OldString = rstAlphaNum![alphaNum]
NewString = ""
For I = 1 To Len(OldString)
If Not IsNumeric(Mid(OldString, I, 1)) Then
NewString = NewString & (Mid(OldString, I, 1))
End If
Next I
rstAlpha.AddNew
rstAlpha![alpha] = NewString
rstAlpha.Update
rstAlphaNum.MoveNext
Loop
End Sub

[OldBirdman]

Change:

For I = 1 To Len(OldString) 
If Not IsNumeric(Mid(OldString, I, 1)) Then 
NewString = NewString & (Mid(OldString, I, 1)) 
End If 
Next I 
 

To:

For I = 1 To Len(OldString) 
If Not IsNumeric(Mid(OldString, I, 1)) Then 
NewString = NewString & (Mid(OldString, I, 1)) 
Else
Exit For
End If 
Next I 
 

[rajeevs]

Great. It works perfect
Thank You So much for the prompt help.

[ADezii]

@rajeevs
Using similar logic:

Dim strNewString As String
Dim rstAlpha As Recordset
Dim rstAlphaNum As Recordset
Dim I As Integer
 
CurrentDb.Execute "DELETE * From tblAlpha", dbFailOnError
 
Set rstAlpha = CurrentDb.OpenRecordset("tblAlpha", dbOpenDynaset)
Set rstAlphaNum = CurrentDb.OpenRecordset("tblAlphaNum", dbOpenDynaset)
 
With rstAlphaNum
  Do While .EOF = False
    strNewString = ""
      For I = 1 To Len(![alphaNum])
        If Not IsNumeric(Mid(![alphaNum], I, 1)) Then
          strNewString = strNewString & (Mid(![alphaNum], I, 1))
        Else
          If strNewString <> "" Then
            rstAlpha.AddNew
              rstAlpha![alpha] = strNewString
            rstAlpha.Update
          End If
            .MoveNext
            Exit For
        End If
      Next I
  Loop
End With
 
rstAlphaNum.Close
rstAlpha.Close
Set rstAlphaNum = Nothing
Set rstAlpha = Nothing
 

P.S. - I did not include Comments so as to not lengthen the code, but should you have any questions, please feel free to ask.

[rajeevs]

Thank you very much once agin for the support