Use of Wild Cards as part of IIF Conditions 1

[khwaja]

I wonder if one can use wild cards as part of the conditions in IIF function. For example, If I wish to return a text "Oranges" to determine whether a column has entries such as

apples
apples and oranges
organges and pears

how could be wild cards used. I have problem in understanding what syntax will be used in such situation.

Kindly help!

 

[tlbroadbent]

Normally, one would use the LIKE predicate in the WHERE criteria of a query or use the INSTR function to detemine if a string exists in a column or field.

Select * From Table
Where col1 like "*oranges*"

Select * From Table
Where Instr(col1, "oranges&quot > 0

Do you have something else in mind? Perhaps, you can post an example if you do.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Thanks for your reply. I am calculating ratings of various stores based on competition surrounding them. There are certain criteria which when true, a rating is given, otherwise zero appears. I am using the following formula.

FSScore: IIf(IsNull([DevType]),0,IIf([DevType]="New" And [Loc Type]="SU FS" And [Type]="Supermarket",10,0, ))))

Here I am saying that if there is a new store being built next to the store and our store is SU FS(suburban freestanding) i.e. not in a shopping centre and it is a supermarket (in contrast with Foodstore), then assign 10 points. As the location Type sometimes is shown as R ST (Regional Freestanding) as a further variant of this category, I would like to be able include all records where Location Type is either SU FS or R FS. Hence, the question whether wild cards can be used in IIF function.

Kind regards

 

[tlbroadbent]

You should be able to use the LIKE predicate and widcards in the IIF function. I tested it in Access 2000.

[Loc Type] LIKE "*FS" Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.