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

Alphabets in a field

100+
P: 171
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
Jul 10 '09 #1
Share this Question
Share on Google+
4 Replies


100+
P: 675
Change:
Expand|Select|Wrap|Line Numbers
  1. For I = 1 To Len(OldString) 
  2. If Not IsNumeric(Mid(OldString, I, 1)) Then 
  3. NewString = NewString & (Mid(OldString, I, 1)) 
  4. End If 
  5. Next I 
  6.  
To:
Expand|Select|Wrap|Line Numbers
  1. For I = 1 To Len(OldString) 
  2. If Not IsNumeric(Mid(OldString, I, 1)) Then 
  3. NewString = NewString & (Mid(OldString, I, 1)) 
  4. Else
  5. Exit For
  6. End If 
  7. Next I 
  8.  
Jul 10 '09 #2

100+
P: 171
Great. It works perfect
Thank You So much for the prompt help.
Jul 10 '09 #3

ADezii
Expert 5K+
P: 8,679
@rajeevs
Using similar logic:
Expand|Select|Wrap|Line Numbers
  1. Dim strNewString As String
  2. Dim rstAlpha As Recordset
  3. Dim rstAlphaNum As Recordset
  4. Dim I As Integer
  5.  
  6. CurrentDb.Execute "DELETE * From tblAlpha", dbFailOnError
  7.  
  8. Set rstAlpha = CurrentDb.OpenRecordset("tblAlpha", dbOpenDynaset)
  9. Set rstAlphaNum = CurrentDb.OpenRecordset("tblAlphaNum", dbOpenDynaset)
  10.  
  11. With rstAlphaNum
  12.   Do While .EOF = False
  13.     strNewString = ""
  14.       For I = 1 To Len(![alphaNum])
  15.         If Not IsNumeric(Mid(![alphaNum], I, 1)) Then
  16.           strNewString = strNewString & (Mid(![alphaNum], I, 1))
  17.         Else
  18.           If strNewString <> "" Then
  19.             rstAlpha.AddNew
  20.               rstAlpha![alpha] = strNewString
  21.             rstAlpha.Update
  22.           End If
  23.             .MoveNext
  24.             Exit For
  25.         End If
  26.       Next I
  27.   Loop
  28. End With
  29.  
  30. rstAlphaNum.Close
  31. rstAlpha.Close
  32. Set rstAlphaNum = Nothing
  33. Set rstAlpha = Nothing
  34.  
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.
Jul 11 '09 #4

100+
P: 171
Thank you very much once agin for the support
Jul 12 '09 #5

Post your reply

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