Need to parse a field with multiple categories (1 Viewer)

Randy · May 12, 2017

I hope this makes sense

I have a payroll feed that sends 9 items in one field. Field name is worktags. I need to parse out the data. I have this so far

EarningDeductionCode: Trim(Mid([worktags],InStr(1,[worktags],"Deduction (Workday Owned):")+27, 25)

So the 25 at the end of the code is just a placement item, as that is the part I cannot figure out.

In order to se the length in my Mid Function I need to replace the "25" with code that will calculate the variable length answer.

There is a carriage return CHR$(10) at the end of each item. So I need to replace the "25" with code that will look for the first carriage return after (for this example) "Deduction (workday Owned): Medicare (ER) [USA]"

Once I figure this out it will work for all 9 items
Business Unit:
Cost Center:
Deduction (Workday Owned):
Employee:
Job Profile:
Location:
Pay Group:
Position:
Category:

the string looks like this (just remember there is a CHR$(10) after each item
so after "Business Unit: IT" there is a carriage return
after "Cost Center: IT HR & Admin Solutions - 68045" there is a carriage return
etc. etc.

Actual data string in field "worktags"
Business Unit: ITCost Center: IT HR & Admin Solutions -68045Deduction (Workday Owned): Medicare (ER) [USA]Employee: John Doe (111015)Job Profile: IT BUSINESS ANALYST IILocation: Wigets - Norcross OfficePay Group: M1APosition: P00001333 IT BUSINESS ANALYSTRun Category: Regular

jdraw · May 12, 2017

Look in to using a Split() function. using CHR$(10) as the separator.

Split(worktags, CHR$(10))

Update: I mocked up your table with 1 record

Code:

Sub demoXSplit()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim arr As Variant
    Dim i As Integer
10  Set db = CurrentDb
20  Set rs = db.OpenRecordset("tblworktagsIssue")
30  Do While Not rs.EOF
40     [COLOR="Blue"][B][SIZE="3"] arr = Split(rs!worktags, Chr$(10))[/SIZE][/B][/COLOR]
50      For i = LBound(arr) To UBound(arr)
60          Debug.Print i, arr(i)
70      Next i
80      rs.MoveNext
90  Loop

End Sub

Results:

Code:

 0            Business Unit: IT

 1            Cost Center: IT HR & Admin Solutions -68045

 2            Deduction (Workday Owned): Medicare (ER) [USA]

 3            Employee: John Doe (111015)

 4            Job Profile: IT BUSINESS ANALYST II

 5            Location: Wigets - Norcross Office

 6            Pay Group: M1A

 7            Position: P00001333 IT BUSINESS ANALYST

 8            Category: Regular

Good luck.

Randy · May 16, 2017

I sort of understand you answer. but I think it is a little beyond my skill set right now. I need to get this done quickly and was hoping to use a basic statement in the Query. But I will use this to teach myself what you are showing for my future projects.

Need to parse a field with multiple categories (1 Viewer)

Randy

Registered User.

jdraw

Super Moderator

Randy

Registered User.

Similar threads

Users who are viewing this thread