Hi all,
I am building a query to display a found record based on a string criteria - easy mode!
Except - If that query does not return a result, then I would like it to return all results (either table or by criteria)... including results where the criteria field is null.
So far I'm trying:
Code:
SELECT [CriteriaField], IIF([CriteriaField] LIKE '*ABC*', [CriteriaField], IIF([CriteriaField] Is Null, 'Empty Record', [CriteriaField]))
FROM MY_Table
However, this query returns all records in the table. I tried using the criteria field also...:
Code:
SELECT CriteriaField
FROM My_Table
WHERE IIF([CriteriaField] LIKE '*ABC*', [CriteriaField], IIF([CriteriaField] Is Null, 'Empty Record', [CriteriaField]))
So.... neither works except to output the entire table....
I need either the result, or everything where the result failed - all tidy in one crazy query.
Any thoughts?
Cheers