Quick IF / OR / LIKE question

[n1ckyr]

Hi, just a quick question, I wondered if there was a quicker way of doing the following:

Expr1: IIf([field1] Like "*criteria1*" Or [field1] Like "*criteria2*",[field2]+20,[field2])

Do I need to be typing the full "or ... like" statement for each criteria or is there another way like putting the criteria in brackets separated by a comma etc.. I've attempted a few ways but just get syntax error.

Thanks for your help

 

[pwbrown]

What are you actually trying to do with this expression?
"*criteria1*" - this will search for records that have the text criteria1 anywhere in it's value.

 

[plog]

Yes, yes you do.

 

[n1ckyr]

I was importing some data with some missing fields and basically wanted to say if Field1 is either this or that (there are about 6 or 7 different possible values in field 1) then add x amount, else do nothing (because I was going to have to look up the other instances manually).

It works the way I have done it but I was curious to know if I had to type [field1] like .... for every criteria or whether there was a quicker way of say putting [field1] like ("*criteria1*", "*criteria2*", etc..).

I was just wondering for future use really if there were more criteria etc...

 

[n1ckyr]

Thanks Plog .. I suspected as much.

 

[pwbrown]

Surely you would use "In" then?
eg. instead of: ([field1] Like "*criteria1*" Or [field1] Like "*criteria2*"
You should be able to do:
[field1] In ("criteria1", "criteria2", "criteria....")

 

[n1ckyr]

Yes, I'd thought about 'in' but the problem is that the data has been input by someone else and they haven't been consistent so sometimes they have abbreviated the to 3 letters and other times have keyed the full word.

I was using the like statement as the first three letters of each type are always the same (I could have done something using the 'left' and 'in' functions but went the 'like' route).

 

[pwbrown]

That's what I wanted explained in the first place.
You could always insert the short and long version into the In.