instr AND

[SteveE]

I have a string field called WCodes which can consist of 1 to 10 Codes ie "W01 , W02 , W03 , W04" ETC

Trying to run an update quert through the dataset to find any that have W01 AND W04 in the string (the rules apply to a few different code mixes but just for this example) as in the case of having both only the W04 can be used, I thought I may use instr but struggling to format it to use an AND (if it can)

so basically looking at field and if there is a W01 and a W04 only use W04

iif(instr([WCodes],"W01" And instr([WCodes],"W04","W04","")))

Can instr be used in this way?, is there a better way?
amy help appriciated

 

[pbaldy]

Shot in the dark:

iif(instr(1,[WCodes],"W01") > 0 And instr(1, [WCodes],"W04") > 0,"W04","")

 

[SteveE]

Thanks Paul certainly works for me , and has given me the basis to tweak it for my use.
steve

 

[pbaldy]

No problem Steve.