Good morning, i've spent the last couple of days researching how to parse a text field in Access using a query. i'm close but not quite 100%
The data field is called description and contains longs statements like
WHO: whoever you want it to be WHAT: the quick brown fox jumps over the lazy dog WHERE: All over the house and everywhere and every when else WHEN: every day and sometimes twice on Tuesday WHY: because he's lazy.
The description field also contains who, what, where, when and why in sentences but the tags are always capitalized and followed by a colon. WHO:, WHAT:, WHERE:, WHEN: and WHY:
i'm trying to break it out into separate fields called D_Who, D_What, D_Where, D_When and D_Why
I can get the Who and Why fields using Left and Right. I've had minimum success with this string to pull the middle arguments
Returns this from one line of data to the D_What field
WHAT: to do the dew for part 2 cant stop the pt rock more pt drill sergeant more pt. More. Pt drills is this really Sergeant the quick brown fox jumps over the lazy dog
but when i change the ""What" and "Where" to "Where" and "Why" it falls apart, it picks up WHERE, WHEN, and WHY.
D_Where: Mid([description],InStr([description],"WHERE:")+0,InStrRev([description],"WHEN:")-InStr([description]," ")+0)
returns this from one line of data to the D_Where field
WHERE: all over the place but you need to do you when you do the dew WHEN: every day WHY: Because
when: every day of every day why: just because