Also pay attention to the spelling: [Promortion Offer] and try to refrain form using spaces and other non-alphanumeric characters as well as reserved words in the names of your objects. All these can lead to errors that are sometimes hard to detect.
2. What do you mean with:
... but without using the cryteria field as in the desing view.
And what's wrong with the IIF expression?
If you need to conditionally select rows from a table, you'll have to use a condition (or criteria) that, in SQL, can be a WHERE clause, an (INNER) JOIN... ON operation or possibly a HAVING clause.
Please specify the structure of the table on which the query is built (columns name and data type).