I have a VBA code in access which I am using to split a string and inserting the results to a table.
the string will have alphabets and numbers. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters. And if the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field. But if the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets extracted to my table. My VBA code is not doing this. Hope someone can guide me what I am missing. Please note that the code I got was from internet and modified to accommodate my use.
Expand|Select|Wrap|Line Numbers
- Dim OldString As String
- Dim NewString As String
- Dim rstAlpha As Recordset
- Dim rstAlphaNum As Recordset
- Dim I As Integer
- DoCmd.RunSQL "Delete * from ResultTbl"
- Set rstAlpha = CurrentDb.OpenRecordset("ResultTbl", dbOpenDynaset)
- Set rstAlphaNum = CurrentDb.OpenRecordset("MyDataTbl", dbOpenDynaset)
- rstAlphaNum.MoveFirst
- Do While rstAlphaNum.EOF = False
- OldString = rstAlphaNum![MyStringitem]
- NewString = ""
- For I = 1 To Len(OldString)
- If Not IsNumeric(Mid(OldString, I, 4)) Then
- NewString = NewString & (Mid(OldString, I, 1))
- End If
- Next I
- rstAlpha.AddNew
- rstAlpha![ExtractedString] = NewString
- rstAlpha.Update
- rstAlphaNum.MoveNext
- Loop
- MsgBox "Finished"
- 'DoCmd.OpenTable "ResultTbl", acViewNormal