Extracting from text

  • Thread starter Thread starter Steven Britton via AccessMonster.com
  • Start date Start date
S

Steven Britton via AccessMonster.com

The below is one recordset how would I go about sniffing through the text
to find the part number? The Part numbers are always 7 digits long, but
there could be more than on in the recordset. Ideas?


Description: 1 each 1701144 RECEPTACLE, DUPLEX IVORY has been added to the
bill of materials for the aft deck receptical. 3 each 1701144 RECEPTACLE,
DUPLEX IVORY have moved from kit 530 to kit 290 1 each 1704934 BOX,
STANDARD WALL BRN has been deleted. 1 each 1697939 COVER PLATE, WEATHER
PROOF GFCI PLAS WHITE has changed from psc 625 to psc 627. 1 each 1743623
COVER PLATE, WEATHER PROOF DUPLEX WHT has moved from psc 625 to psc 627. 1
each 1791613 RECEPTACLE, GFI IVORY W/O LEADS in psc 627 has moved from kit
621 to kit 530. 1 each 1697939 COVER PLATE, WEATHER PROOF GFCI PLAS WHITE
has moved from psc 625 to psc 627. 1 each 1743623 COVER PLATE, WEATHER
PROOF DUPLEX WHT has moved from psc 625 to psc 627.
 
You would need to use a VBA function to do this.

In what form are the part numbers to be returned -- as a concatenated (with
delimiters) string? individually? as a array of values? In what context
would you be extracting these numbers? how will you be using the output?
 
The below is one recordset how would I go about sniffing through the text
to find the part number? The Part numbers are always 7 digits long, but
there could be more than on in the recordset. Ideas?

Description: 1 each 1701144 RECEPTACLE, DUPLEX IVORY has been added to the
bill of materials for the aft deck receptical. 3 each 1701144 RECEPTACLE,
DUPLEX IVORY have moved from kit 530 to kit 290 1 each 1704934 BOX,
STANDARD WALL BRN has been deleted. 1 each 1697939 COVER PLATE, WEATHER
PROOF GFCI PLAS WHITE has changed from psc 625 to psc 627. 1 each 1743623
COVER PLATE, WEATHER PROOF DUPLEX WHT has moved from psc 625 to psc 627. 1
each 1791613 RECEPTACLE, GFI IVORY W/O LEADS in psc 627 has moved from kit
621 to kit 530. 1 each 1697939 COVER PLATE, WEATHER PROOF GFCI PLAS WHITE
has moved from psc 625 to psc 627. 1 each 1743623 COVER PLATE, WEATHER
PROOF DUPLEX WHT has moved from psc 625 to psc 627.
Click to expand...

Create a user defined function.
The only consistent phrase I can see above is the word 'each'.
So search in the text for the word each.
The part number will be in the next 8 characters after that word, i.e.
(each + space + 7 digits).

Function FindNumbers(StrIn As String) As String
Dim intX As Integer
Dim intY As Integer
Dim strParts As String
If IsNull(StrIn) Then
Exit Function
End If
intY = 1
intX = InStr(StrIn, "each")
Do While intX <> 0
strParts = strParts & Mid(StrIn, intX + 5, 7) & ","
intY = intX + 1
intX = InStr(intY, StrIn, "each")
Loop
If Len(strParts) > 1 Then
strParts = Left(strParts, Len(strParts) - 1)
FindNumbers = strParts
End If
========
Call it from a query:
PartNumbers:FindNumber([FieldName])

Add error handling as required.

Your above test results in the following data:
1701144,1701144,1704934,1697939,1743623,1791613,1697939,1743623
 
Hi Steve,

It looks as if you need to search for seven-digit numbers preceded by
the word "each" and the sensible way to do that sort of thing is to use
regular expressions. Something like this:


Function TestREG(S As Variant) As Variant
Dim oRE As Object 'RegExp
Dim oMatches As Object 'MatchCollection
Dim oMatch As Object 'Match
Dim strOnePartNumber As String

Set oRE = CreateObject("VBScript.Regexp")

oRE.Global = True
oRE.Multiline = True
oRE.Pattern = "each (\d{7})"
Set oMatches = oRE.Execute(Nz(S, ""))
For Each oMatch In oMatches
strOnePartNumber = oMatch.SubMatches(0)
Debug.Print strOnePartNumber
Next
End Function
 
Ken,

Thanks for the response - and to the other after as well. This field is a
descprition field from an Export out of a Lotus Notes database. So the
descprition is for an Engineering Change Notice that has a unique number.
What I want to do is tie the ECN number to all of the part numbers listed
in the description field. So I am thinking currently tblECNNumbers one to
many tblpartsaffected - or something of the sort.

So I would probably need them in an array I am assuming that I would use a
Union Query to Append them into the table. Additionally, they are not
aways going to have each in front of them. Here are two more examples of
description fields. Thanks agian, without you I don't know what I would be
able to do. MVP's deserve medals along with their paychecks...

Description: To follow up with an engineering revision Iman #214264 has
been replaced with Iman #215006 Head Floor. The CNC revision's were made,
however Access and The Unigraphics drawings were not updated. In addition
the documentation on Dwg #214847 Bunk Top The material description for Iman
# 215006 will be adjusted to reflect actual material used. Item # 11 is
currently PN 1697708 1/2 " Almond and will be replaced with PN 1701322 3/4"
Plywood . No BOM Revisions

Description: Need to have a hole tabbed on HH173650-1.12 S2P2. The
location of the hole is 12" above the light switch hole and the same width.
The length is 3-5/8". The opt is not on every boat but if we could have
the corners drilled in proper location we can cut it out when we have the
option.
 
Paychecks? Now that would be intriguing... no paychecks here!

OK - it appears that you would want to use VBA code to get the numbers
extracted from the description, and then you could run the desired query
from code, building it from the returned information.

This is all air code, so you may need to tweak, but a function that would
get the extraction could be this:


Public Function ExtractXDigitNumbers(strOriginalDescription As String, _
intNumOfDigits As Integer, strDelimiterToUse As String) As String
Dim intLoop As Integer
Dim lngDescLen As Long, lngLocation As Long
Dim strNumberString As String, strParsedString As String
Dim strCompareString As String
Const strNumCompare As String = "[0-9]"
strNumberString = ""
strCompareString = ""
lngLocation = 1
' Get length of original string
lngDescLen = Len(strOriginalDescription)
' Build comparison string
For intLoop = 1 To intNumOfDigits
strCompareString = strCompareString & strNumCompare
Next intLoop
' Loop through string to parse out numbers
Do While lngLocation <= lngDescLen - intNumOfDigits + 1
strParsedString = Mid(strOriginalDescription, lngLocation,
intNumOfDigits)
If strParsedString Like strCompareString Then
If Len(strNumberString) > 0 Then strNumberString = _
strNumberString & strDelimiterToUse
strNumberString = strNumberString & strParsedString
lngLocation = lngLocation + intNumOfDigits
Else
lngLocation = lngLocation + 1
End If
Loop
ExtractXDigitNumbers = strNumberString
End Function

You would call this function to get a delimited list of numbers back (you
specify the delimiter character). Then you can split the string using the
Split function into a variant array:

Dim varTheNumbers As Variant
varTheNumbers = Split(ExtractXDigitNumbers("ActualDescriptionStringText", _
7, "|"), "|")

Now varTheNumbers will be an array containing each number in a separate
value of the array.
 
Back
Top