467,092 Members | 1,148 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,092 developers. It's quick & easy.

Extract from string

Byte
Hi All
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
  1.  Dim OldString As String
  2.     Dim NewString As String
  3.     Dim rstAlpha As Recordset
  4.     Dim rstAlphaNum As Recordset
  5.     Dim I As Integer
  6.     DoCmd.RunSQL "Delete * from ResultTbl"
  7.  
  8.     Set rstAlpha = CurrentDb.OpenRecordset("ResultTbl", dbOpenDynaset)
  9.     Set rstAlphaNum = CurrentDb.OpenRecordset("MyDataTbl", dbOpenDynaset)
  10.  
  11.     rstAlphaNum.MoveFirst
  12.  
  13.     Do While rstAlphaNum.EOF = False
  14.         OldString = rstAlphaNum![MyStringitem]
  15.         NewString = ""
  16.         For I = 1 To Len(OldString)
  17.  
  18.  
  19.          If Not IsNumeric(Mid(OldString, I, 4)) Then
  20.              NewString = NewString & (Mid(OldString, I, 1))
  21.  
  22.           End If
  23.         Next I
  24.         rstAlpha.AddNew
  25.         rstAlpha![ExtractedString] = NewString
  26.         rstAlpha.Update
  27.         rstAlphaNum.MoveNext
  28.     Loop
  29.     MsgBox "Finished"
  30.     'DoCmd.OpenTable "ResultTbl", acViewNormal
1 Week Ago #1

✓ answered by cactusdata

  1. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters.
  2. If the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field.
  3. If the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets.
1 and 3 are the same, so you can cut it down to check for a digit in the first three characters.
If found, pull the full string; if not pull the first three letters if the fourth is a digit:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExtractThreeOrMore(ByVal Value As String) As String
  2.  
  3.     Dim Position    As Integer
  4.     Dim Result      As String
  5.     Dim DigitExists As Boolean
  6.  
  7.     If Len(Value) >= 3 Then
  8.         For Position = 1 To 3
  9.             If IsNumeric(Mid(Value, Position, 1)) Then
  10.                 DigitExists = True
  11.                 Exit For
  12.             End If
  13.         Next
  14.     End If
  15.  
  16.     If DigitExists Then
  17.         Result = Value
  18.     Else
  19.         If IsNumeric(Mid(Value, 4, 1)) Then
  20.             Result = Left(Value, 3)
  21.         End If
  22.     End If
  23.  
  24.     ExtractThreeOrMore = Result
  25.  
  26. End Function
Now, use any method to insert the result in your table.

  • viewed: 2198
Share:
6 Replies
isladogs
Expert 64KB
Please give some sample data and the required result in each case to clarify what you want.
1 Week Ago #2
NeoPa
Expert Mod 16PB
On similar lines as IslaDogs - What does your question even mean. I've read it a couple of times and all I got was head pain & confusion.

I get that when you refer to alphabets you are actually referring to alphabetic characters (Or more simply just letters.) but the rest was so unclear I gave up.

Before I leave you with just this complaint let me just add that we are probably going to be able to help once we have a decent question to work with. I'd be surprised if the use of the Like comparison didn't come into it somewhere, but more detail than that will depend on the actual requirement.
1 Week Ago #3
cactusdata
Expert 128KB
  1. If the first 3 characters are alphabets and the 4th character is a number I need to extract the first 3 characters.
  2. If the string has all alphabets or numbers or numbers before 3rd characters, insert the same string to the field.
  3. If the string has 3 alphabets, then numbers and then alphabets, I need only the first 3 alphabets.
1 and 3 are the same, so you can cut it down to check for a digit in the first three characters.
If found, pull the full string; if not pull the first three letters if the fourth is a digit:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExtractThreeOrMore(ByVal Value As String) As String
  2.  
  3.     Dim Position    As Integer
  4.     Dim Result      As String
  5.     Dim DigitExists As Boolean
  6.  
  7.     If Len(Value) >= 3 Then
  8.         For Position = 1 To 3
  9.             If IsNumeric(Mid(Value, Position, 1)) Then
  10.                 DigitExists = True
  11.                 Exit For
  12.             End If
  13.         Next
  14.     End If
  15.  
  16.     If DigitExists Then
  17.         Result = Value
  18.     Else
  19.         If IsNumeric(Mid(Value, 4, 1)) Then
  20.             Result = Left(Value, 3)
  21.         End If
  22.     End If
  23.  
  24.     ExtractThreeOrMore = Result
  25.  
  26. End Function
Now, use any method to insert the result in your table.
1 Week Ago #4
Byte
Hi cactusdata
Thank you for the provided function. I used it in a query and it looks like it is giving expected results
The syntax of query as "SELECT data.alphabets, ExtractThreeOrMore([alphabets]) AS Result
FROM data;"
The sample records in the table as below
Field name: alphabets

1NTRWE
NNB3456
7DCXD
NNB67FX
DUC6
LIY683
LIY9RT
MM2345
LO8765F

And the results I got in the query as below
Field Name: Result
1NTRWE
NNB
7DCXD
NNB
DUC
LIY
LIY
MM2345
LO8765F

I think it is working fine. I need to test it with large data and also need to try how to add this function in my old code.
Thank you so much for interpreting my question correctly and provided a quick solution. 'bytes' always very supportive
1 Week Ago #5
cactusdata
Expert 128KB
You are welcome! Get back if you have questions.
1 Week Ago #6
Bit
Wow thanks for share
1 Week Ago #7

Post your reply

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

Similar topics

9 posts views Thread by Sharon | last post: by
12 posts views Thread by rshepard@nospam.appl-ecosys.com | last post: by
1 post views Thread by josephtys86@googlemail.com | last post: by
1 post views Thread by Edwin.Madari@VerizonWireless.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.