Alphabets in a field

[rajeevs]

Hi
Pls anyone can help me to find only the alphabets from a field in MS ACCESS
eg. if the field is abcd123 i need to retrieve only abcd
Pls help.....

[missinglinq]

What you need to do is to loop thru your original string one character at a time and keep the character if it is not numereic. This will do it:

Dim OldString As String
Dim NewString As String
NewString = ""
OldString = "ab9cd12e3"
 
For I = 1 To Len(OldString)
  If Not IsNumeric(Mid(OldString, I, 1)) Then
     NewString = NewString & (Mid(OldString, I, 1))
  End If
Next I 
 

Good Luck!

[rajeevs]

Hi
Thanks for the reply. But I want to use it in a query. the query need to retrieve the the data without the numbers from the table field
eg: if the field contains records with alphabets and numbers i need to get the result of query as only alphabets
ie; abcd123, ghi2333, foxwer1 results should be abcd, ghi, foxwer like that
please help...

Thank you

[rajeevs]

Hi
Thank u for the kind reply. But i tried that code. i will explain what i am doing. i have a database and the main table got a field which holds records like abcd1234, ghj36789. i want to create a new table using a module with a single field that should have only the alphabet part of the main table like abcd, ghj.
i can create a table using a module, but i tried your code but no effect.
Hope you can help me pls..........

Thank u

[JKing]

Hi, I added to the previous code posted to help you loop through the main table and store the new strings into your second table. Make sure you have the second table created with the single field before attempting this.

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
 

Notes:
tblAlphaNum is your main table and [alphaNum] is the field containing your alpha numeric code
tblAlpha will be your second table and [alpha] is the field you want to store your new alpha codes in

Hope this helps set you in the right direction.

[rajeevs]

Thank u so much
It works

Raj