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
Query format
D_What: Mid([description],InStr([description],"WHAT:")+0,InStrRev([description],"WHERE:")-InStr([description],":")-10)
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.
Query format
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
