question

rbsuttn avatar image
rbsuttn asked

Assistance with parsing a text field in access

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

access
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
bkadam avatar image
bkadam answered

It is a great pleasure to read your message. It's full of information I'm looking for and love to post a comment that says "The content of your post is amazing". Excellent work.

For SAP ABAP Training in Pune Please Visit - https://www.sevenmentor.com/sap-abap-course-in-pune.php

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.