Stripping data in a query

[jimday1982]

I'm using a bunch of different functions to strip out certain data in my field "wwe-caption". The expression I'm using is:


IIf(InStr([M].[wwe-caption],"<b>Order")<>0,Left([M].[wwe-caption],Len(Left([M].[wwe-caption],InStr([M].[wwe-caption],"<b>Order")))-1),IIf(InStr([M].[wwe-caption],"<font color=")<>0,Left([M].[wwe-caption],InStr([M].[wwe-caption],"<font color=")-1),[M].[wwe-caption]))


That strips out anything in the field that begins with "<b>Order" or "<font color=". The problem is now I need to strip out data that begins with "<b>Delivery:". I added more code to do so at the end of that expression, but it didn't work because I think it was dropping out of one of the previous iif statements. This expression is at a point where I can't really understand it anymore, so if anyone has any ideas, I'd greatly appreciate it.

 

[Kodo]

use regular expressions
http://www.4guysfromrolla.com/webtech/090199-1.shtml
4GuysFromRolla.com - An Introduction to Regular Expression with VBScript

You can use the VBScript they show you here exactly in VBA.. you only need to reference Regular Expressions for vb script 5.5 .

 

[JMG]

Place your last Iif statement

after the last comma (before the "else" part of the code) - make sure you put a comma behind it before the "else". place a closing ")" and you've got it

 

[jimday1982]

JMG, I tried what you suggest:

wwe-caption: IIf(InStr([M].[wwe-caption],"<b>Order")<>0,Left([M].[wwe-caption],Len(Left([M].[wwe-caption],InStr([M].[wwe-caption],"<b>Order")))-1),IIf(InStr([M].[wwe-caption],"<font color=")<>0,Left([M].[wwe-caption],InStr([M].[wwe-caption],"<font color=")-1),IIf(InStr([M].[wwe-caption],"<b>Delivery:")<>0,Left([M].[wwe-caption],InStr([M].[wwe-caption],"<b>Delivery:")-1),[M].[wwe-caption])))

But it didn't strip it out - I cannot figure out why. Does anything stick out at you?

Kodo, thanks for the tip - I will look into that, but right now, I don't have the time.

 

[jimday1982]

Problem solved using:

wwe-caption: IIf(InStr([M].[wwe-caption],"<b>Delivery:")<>0,Left([M].[wwe-caption],InStr([M].[wwe-caption],"<b>Delivery:")-1),IIf(InStr([M].[wwe-caption],"<b>Order")<>0,Left([M].[wwe-caption],Len(Left([M].[wwe-caption],InStr([M].[wwe-caption],"<b>Order")))-1),IIf(InStr([M].[wwe-caption],"<font color=")<>0,Left([M].[wwe-caption],InStr([M].[wwe-caption],"<font color=")-1),[M].[wwe-caption])))