Pull Text From String

[bobreyn1990]

Hi All - lots of info in here but am hoping to see if this can be done someplace in Access - thinking a query most likely. I have a text field in a Table and on a Query called "Notes" In that field that has data like below:

[04/02/2015:BD] Project is to be assessed by Solutions Planning
[03/27/2015:BD] Project prioritized
[03/14/15:BR] Entered to system

Im trying to find a way to pull just the most recent line of text, in this case

[04/02/2015:BD] Project is to be assessed by Solutions Planning

into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.

Any thoughts out there?

 

[KenHigg]

Sounds like a reasonable plan. I would do it with a function -

 

[CJ_London]

if the latest item is always on the top row you can use

left(mymemo,instr(mymemo,chr(13))-1)

 

[bobreyn1990]

Hi guys - thanks. Let me add a little clarity

In the case I described, the text Im looking to pull would likely be of varying lengths. Example

In 1 record the text may be 15 characters long....the next...maybe 9. would this "left(mymemo,instr(mymemo,chr(13))-1)" acocunt for the varying lenght?


Hi Ken - can you give me a shove in the right direction on a function?

 

[CJ_London]

would this "left(mymemo,instr(mymemo,chr(13))-1)" acocunt for the varying lenght?

I wouldn't have suggested it otherwise - why don't you try it and see?

 

[bobreyn1990]

Hi CJ - I did, yes and I am seeing a few errors so thought that was it. Im seeing #Func! for some fields...others seem to be pulling ok Is the 13 in the formula counting the date in the brackets?

example:

this text string: [3/26/2015:CMB] Sent email requesting estimate on when GHR expects to be ready to begin Technical Assessment.
[3/20/2015:CMB] No change, will follow up this week

returns #Func! when I looking to pull the first text only

 

[CJ_London]

can you copy the exact code you are using since I cannot debug my own code which I know is correct.

chr(13) is the carriage return character,

instr identifies the first position it is found in a string - which in the example below should be after the fullstop after the word 'assessment'

[3/26/2015:CMB] Sent email requesting estimate on when GHR expects to be ready to begin Technical Assessment.
[3/20/2015:CMB] No change, will follow up this week

just thought, you will get an error if a carriage return is not found - e.g. perhaps if there is only one 'entry'.

Have a look at those where you are getting an error and see if that is the case, if so, I can suggest a mod as follows

iif(instr(mymemo,chr(13))=0,mymemo,left(mymemo,instr(mymemo,chr(13))-1)")

 

[bobreyn1990]

Hi -

Your modified formula cleared up the errors but it looks like this: IIf(InStr([Notes],Chr(13))=0,[Notes],Left([Notes],InStr([Notes],Chr(13))-1))

Access didnt like the ") at the end for some reason - invalid string?

[Notes] is the field that has the text entries so next to the Notes field in the query is this entry: Expr1: IIf(InStr([Notes],Chr(13))=0,[Notes],Left([Notes],InStr([Notes],Chr(13))-1))

I also went back to the source file to put a carriage return in where they should be, just in case, but it still pulled all the text. Some progress though - im still playing around with it

 

[Brianwarnock]

Hi -

Your modified formula cleared up the errors but it looks like this: IIf(InStr([Notes],Chr(13))=0,[Notes],Left([Notes],InStr([Notes],Chr(13))-1))

Access didnt like the ") at the end for some reason - invalid string?

[/ QUOTE]

It was almost certainly a typo by CJ , probably he did a bit of copy and paste from post #4

Brian

 

[bobreyn1990]

Agreed - Im still playing around with this. Hopefully I can get it to work. This issue may be in the soruce file itself so Im looking in to that

 

[CJ_London]

my mistake - a superfluous " at the end