instr AND (1 Viewer)

SteveE

Registered User.
Local time
Today, 16:02
Joined
Dec 6, 2002
Messages
221
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

Wino Moderator
Staff member
Local time
Today, 09:02
Joined
Aug 30, 2003
Messages
36,118
Shot in the dark:

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

SteveE

Registered User.
Local time
Today, 16:02
Joined
Dec 6, 2002
Messages
221
Thanks Paul certainly works for me , and has given me the basis to tweak it for my use.
steve
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:02
Joined
Aug 30, 2003
Messages
36,118
No problem Steve.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:02
Joined
Feb 19, 2002
Messages
42,970
Of course properly normalizing the schema is ultimately a better solution. Rather than having multiple values stored in a single column, you need a separate table which will be a 1-m relationship with the original table.
 

Users who are viewing this thread

Top Bottom