Extract Data from Memo Field

  • Thread starter Thread starter meileigh
  • Start date Start date
M

meileigh

I have a spreadsheet that is imported into Access. The last field is imported
as a Memo filed due to the large amount of data.
I need to be able to parse out specific data from that memo field while
retaining the same Ticket #.

Example: Two columns of data
Ticket #-Text Field-Unique identifier
SC Information-Memo filed-contains all notes from Ticket#.

I need to extract the Asset Tag(s) for each Ticket # from the SC Information
field. There can be more that one Asset tag per Ticket #. The Asset Tag is
formatted as a “W†with 7 digits after (W1234567)

Ticket # 1234567
SC - Information
"02/09/2009 14:15 - Brown,W
Client has router config to use for new router

02/05/2009 16:49 - Mcdowell,Don,
Shipping Replacement: S&R ROUTER
P1
Ground Return:

02/05/2009 12:00 - Brown,William,M

02/04/2009 17:00 - Mcdowell,Don,
Shipping Replacement: DELL LATITUDE D630
Asset Tag: W1234567

02/03/2009 16:26 - Moseley,Chris,M
Item Needed: S&R
Asset: W7654321
Make: DELL
Model: LATITUDE D630
Store Location Number: 414
Hardware Asset: W9999999
Available Tomorrow: All Day"
 
UNTESTED UNTESTED UNTESTED
Create an Asset table --
TicketAssetID - autonumber - primary key
TicketID - number - long integer - foreign key
Asset - text
Location - number - long integer

Append query --
INSERT INTO Asset ( TicketID,Asset , Location )
SELECT [Ticket_SC Information_Table].TicketID, Mid([SC Information],
InStr(Mid([SC Information], Nz([Location],0), "Asset: W"), 8) AS Asset,
Location
FROM [Ticket_SC Information_Table] LEFT JOIN Asset ON [Ticket_SC
Information_Table].TicketID = Asset.TicketID
WHERE ([Ticket_SC Information_Table].TicketID Is Null) OR (InStr(Mid([SC
Information], Nz([Location],0), "Asset: W"), 8)>0 );


You will need to run the append query for as many times as you have assets
on a ticket.
 
Do you want the Asset Tags to be returned in separate results or can you
except them in one delimited result?

Are you planning to create a new table with fields Ticket# and Asset Tag. And
have one record for each combination of Ticket# and Asset Tag?

You might be able to use a custom function like this UNTESTED one. Copy this
to a VBA module and save.

'=======================================================================
Public Function fGetTag(strIn As Variant, Optional iWhichOne As Long = 0)
'If iWhichone is zero, return all the asset tags in a space delimited list
Dim vResult As Variant: vResult = Null
Dim iPos As Long, iLoop As Long
Dim vArray As Variant

If Len(Trim(strIn)) = 0 Then
vResult = Null
Else
vArray = Split(strIn, " ")
For iLoop = LBound(vArray) To UBound(vArray)
If vArray(iLoop) Like "W#######" Then
If iWhichOne = 0 Then
vResult = vResult & " " & vArray(iLoop)
Else
iPos = iPos + 1
If iPos = iWhichOne Then
vResult = vResult & " " & vArray(iLoop)
Exit For
End If
End If
End If
Next iLoop
End If

fGetTag = Mid(vResult, 2)
End Function
'====================================================================

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top