By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,562 Members | 940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,562 IT Pros & Developers. It's quick & easy.

Alphabets in a field

P: 171
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.....
Jun 12 '07 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. Dim OldString As String
  2. Dim NewString As String
  3. NewString = ""
  4. OldString = "ab9cd12e3"
  6. For I = 1 To Len(OldString)
  7.   If Not IsNumeric(Mid(OldString, I, 1)) Then
  8.      NewString = NewString & (Mid(OldString, I, 1))
  9.   End If
  10. Next I 
Good Luck!
Jun 12 '07 #2

P: 171
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
Jun 14 '07 #3

P: 171
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
Jun 21 '07 #4

Expert 100+
P: 1,206
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRun_Click()
  2.     Dim OldString As String
  3.     Dim NewString As String
  4.     Dim rstAlpha As Recordset
  5.     Dim rstAlphaNum As Recordset
  6.     Dim I As Integer
  8.     Set rstAlpha = CurrentDb.OpenRecordset("tblAlpha", dbOpenDynaset)
  9.     Set rstAlphaNum = CurrentDb.OpenRecordset("tblAlphaNum", dbOpenDynaset)
  11.     rstAlphaNum.MoveFirst
  13.     Do While rstAlphaNum.EOF = False
  14.         OldString = rstAlphaNum![alphaNum]
  15.         NewString = ""
  16.         For I = 1 To Len(OldString)
  17.           If Not IsNumeric(Mid(OldString, I, 1)) Then
  18.              NewString = NewString & (Mid(OldString, I, 1))
  19.           End If
  20.         Next I
  21.         rstAlpha.AddNew
  22.         rstAlpha![alpha] = NewString
  23.         rstAlpha.Update
  24.         rstAlphaNum.MoveNext
  25.     Loop
  26. End Sub
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.
Jun 21 '07 #5

P: 171
Thank u so much
It works

Jun 26 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.