Why won't my criteria work

[Geoff Codd]

I have the following criteria in a query but it won't work

IIf([Forms]![Report Criteria Selection]![Sub-Group_Selection]="Gas (All Sites) & Water (All Sites)",Like "Direct Reading - Gas*" Or Like "Direct Reading - Water*",Like [Forms]![Report Criteria Selection]![Sub-Group_Selection])

When I seperate the criteria and test

Like "Direct Reading - Gas*" Or Like "Direct Reading - Water*"

And

Like [Forms]![Report Criteria Selection]![Sub-Group_Selection]

They both work but as soon as I include then in an IIF statement it won't.

Any ideas anyone

Thanks
Geoff

 

[ColinEssex]

Hi Geoff

I may be wrong, but I don't think you can put If or IIf statements in the criteria of a query. I remember trying it and failing and I think Pat said the same thing.

Col

 

[Jon K]

Try this setting in a column in the query grid (using the correct field name before the word Like in three instances):-

Field: IIf([Forms]![Report Criteria Selection]![Sub-Group_Selection]="Gas (All Sites) & Water (All Sites)", [FieldName] Like "Direct Reading - Gas*" Or [FieldName] Like "Direct Reading - Water*", [FieldName] Like [Forms]![Report Criteria Selection]![Sub-Group_Selection])

Show: uncheck
Criteria: <>False


<>False is one of Access's defaults. It tells Access the IIf expression in the Field: cell is a Criteria.

Hope this helps.

 

[Geoff Codd]

Thanks Jon

You learn something new every day

Geoff