Strip out everything after a string

[jimday1982]

Is there a function that will strip out everything in a field after a given string. For instance...in one of my fields I have:

Cool product that does this<b>order notes - ships in 2 days

and I want it to return:

Cool product that does this

So I'm trying to strip out everything after and including: <b>order notes - ships in 2 days

Any ideas?

 

[Calvin]

Use an IIF function to make the determination of what is returned, ie.

=IIF(Left([Field1],Len('Cool product that does this')='Cool product that does this', 'Cool product that does this', [Field1])

if you don't understand the syntax of the IIF function search this forum or look it up in Accesses VBA Help file

 

[jimday1982]

Thanks for your reply, however that's not going to work. I should have mentioned that this expression will need to be ran on a few hundred records with varying amounts of data in each field. I cant have it being compared to a specific string...it needs to strip out everything after a certain string - no matter what the offending data contains or how much data there is.

 

[Calvin]

"it needs to strip out everything after a certain string"
where does this "certain string" come from?

 

[jimday1982]

The string is "<b>Order Notes: * " where * equals everything else. Any ideas?

 

[Calvin]

is the data in question always in the same location in the string, for example starting at the 15th character? or ending at the 20th character? or does it float in the string meaning it can be located anywhere in the string? in either of these cases you can write a custom function in a module and pass the string to the function and have the function evaluate it and return it back to the query.

 

[Mile-O]

Use the InStr function in conjunction with either the Left(), Mid(), or Right() functions.

 

[jimday1982]

Great idea...I got it to work with the following expression:

expr1: IIf(InStr([caption],"<b>Order")<>0,Left([caption],InStr([caption],"<b>Order")),[caption])

However, there is a problem...I HAVE to use "caption" instead of "expr1" but it won't let me due to a "circular reference caused by the alias"...any ideas on how to get around that?

 

[Calvin]

Nope, [caption] is the name of your source field, since your using a comparison expression you will have to rename the field of your newly evaluated data with another name as not to confuse with a Circular Reference otherwise it will try to evaluate itself. This is normal. You can name the new field to anything you want so instead of "expr1" maybe instead something like "NewCaption" after all it is new data.

 

[jimday1982]

Thanks. Actually I found out that if I reference the table name AND the field in the expression, I can still use caption as the alias, so I got lucky. One thing I did notice is that there is a trailing "<" in all of my data that was supposed to only take the left portion of everything after <b>Order...this is very odd because I am testing correctly in the expression...Any ideas what could cause this?

 

[raskew]

 IIf(InStr([caption],"<b>Order")<>0,Left([caption],InStr([caption],"<b>Order")[COLOR=Red]-1[/COLOR]),[caption])

Without the -1, the '<' is included in the expression.

HTH - Bob

 

[raskew]

Jim Day-

You've shot-gunned your problem over at least three forums

that I've noted (maybe more). Are your issues resolved?

It'd be nice if you'd come back to contributors and say

something to the effect of ...(this solution [myHyperlink]

resolved my issues. Thank you very much for your assistance.)

Bob