Solved

Extract Characters From String Based On Format (Part 2)

Posted on 2011-03-08
8
236 Views
Last Modified: 2012-08-13
Hi, using a query I need to be able to extract a code from within a character string. There can be several thousand lines, each with a different code but the format is always the same ####-. Here is an example:

Desc
10-403 Sal-RecruitInit 1485- Staff Recharge
10-413 Sales- Creative 1239- Specs UK advert
10-415 Marketing Man   1239- Specs UK advert
10-416 SaleProfPublica 1152- Professional Ma
10-416 SaleProfPublica 1153- Optom Career Pa
10-416 SaleProfPublica 1938- ODP Income
10-416 SaleProfPublica 1947- Nat Sem Inc
10-417 Sales - Recall  1239- Specs UK advert
10-421 Sales-Training  1048- Emplymnt Law Ma
10-421 Sales-Training  1049- Rectmnt Mat Inc
10-421 Sales-Training  1161- SmartBus RS In
10-421 Sales-Training  1215- Tra Fees - Gen
10-421 Sales-Training  1327- Conducting Staf
30-537 Gen Exp - PA Co 3008-0008 Travel-Mileage

From line 1 I would be extracting 1485, line 2 1239 and so on. Occasionally the code within the string is formatted as ####-#### (as in the last example).

e.g. 2588-4876

In those cases I need the first four, and the second four.

The following code works well to extract the first four characters:

Function GetNos(pstring As String) As String
Dim x As Integer, ln As Integer, ch As String
Dim res As String
GetNos = ""
ln = Len(pstring)
If ln < 7 Then Exit Function
For x = 6 To ln
    ch = Mid(pstring, x, 1)
    If ch >= "0" And ch <= "9" Then
        res = res & ch
    ElseIf Len(res) = 4 And ch = "-" Then
       
        Exit For
    Else
        res = ""
    End If
   
Next x
If Len(res) = 4 Then GetNos = res
End Function
0
Comment
Question by:jonlake
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 35068764
Do you want the first and second codes as separate fields?
0
 

Author Comment

by:jonlake
ID: 35068808
Yes please, if at all possible
0
 
LVL 17

Accepted Solution

by:
JezWalters earned 500 total points
ID: 35068814
If so, you could create separate functions to extract each code, like this:
Option Explicit
Option Compare Database

Public Function ExtractCode1(pstrText As String) As String

    Dim bytCharacter As Byte

    For bytCharacter = 7 To Len(pstrText)
        If IsNumeric(Mid(pstrText, bytCharacter, 1)) Then
            Exit For
        End If
    Next
    ExtractCode1 = Mid(pstrText, bytCharacter, 4)

End Function

Public Function ExtractCode2(pstrText As String) As String

    Dim bytCharacter As Byte

    For bytCharacter = 7 To Len(pstrText)
        If IsNumeric(Mid(pstrText, bytCharacter, 1)) Then
            If IsNumeric(Mid(pstrText, bytCharacter + 5, 1)) Then
                bytCharacter = bytCharacter + 5
                Exit For
            End If
        End If
    Next
    ExtractCode2 = Mid(pstrText, bytCharacter, 4)

End Function

Open in new window

0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35068819
You can invoke the functions from a query like this:
SELECT YourField, ExtractCode1(YourField) AS Code1, ExtractCode2(YourField) AS Code2
FROM YourTable

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:JezWalters
ID: 35068821
How's that for you?
0
 

Author Comment

by:jonlake
ID: 35072617
I will be able to try this tomorrow back in the office.
0
 

Author Closing Comment

by:jonlake
ID: 35080335
Thanks Jez, great work!
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35083593
No offense to peter57r, but where there's a choice it's usually best to go with the solution that works that also has the least code (see your related question).  Entia non sunt multiplicanda praeter necessitatem - in other words, less is more!  ;-)

Anyway, glad I could be of assistance!  :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now