iif/nz function substitude in t-sql

Well, to start with, using IIf() and Nz() to manage several criteria is bad practice in Access. Any function call in a WHERE clause will prevent optimization... Instead:

    WHERE [choose model] Is Null Or [choose model] = Model
        AND [choose year] Is Null Or [choose year] = YearBuilt
        AND [choose colour] Is Null Or [choose colour] = Colour

Since the "choose *" are constants, the entire criteria will be wiped out during the query preparation when they are null; if not, an index can be used.

The same logic applies to all SQL engines: if possible, use only 'And', 'Or', and comparison operators. No CASE, no CAST, no functions!

(°v°)

I'd certainly agree with that. It's a pet moan of mine how often you'll see advized criteria which includes a function call upon a field.
The classic example is when someone wants a Date only match in a field which includes Time components.
e.g. WHERE DateValue(DateField) = #12/12/2009#
Ow!

However, there are times when you're just stuck. To achieve what you want by other means, you'd have to be storing denormalized data. So a function call (/expression) it is.

Equally, forming involved comparisons can be a problem, where it's safe to say that "AND" is a more friendly operator than "OR".
If you're going to include OR compared criteria then both fields need to be indexed.
e.g. WHERE Field1 = 10 OR Field2 = 'Fred'
employs index optimisation if both Field1 and Field2 are indexed. (In Jet it's Rushmore optimisation - in other RDBMS there'll likely be a whole mess of considerations I'd say, including uniqueness of indexes, columns returned, table statistics etc :-s).

In general (and in Jet) this extends to comparing constant values (after all, they're not indexed ;-).
This is why I, personally, don't discourage ad-hoc SQL built at runtime (if it's with good reason of course - doing it as a general practice makes no sense to me at all. You'll hear people say "I don't like a cluttered database window"... Huh? But a cluttered VBE is OK? :-s).
Yes saved queries have advantages but, to my mind, query optimisation is more important.
When we work to make parameters as close to optional as we can in Jet, we would lose that optimisation.

WHERE (FieldName1 = [Param1] OR [Param1] Is Null) AND (FieldName2 = [Param2] OR [Param2] Is Null)

is indeed that Jet version of optionality (which in T-SQL, say, could be implemented optionally with dynamic SQL or entirely conditional statements executing only the one matching the passed parameters - which is probably what you refer to as the heaps of code you're trying to avoid. Sometimes more code provides a more efficient database and application!)

If you examine the parameter you're going to pass before doing any query work and build your SQL based on that then you lose pre-compiled execution plan. But you can build a more optimisable statement. Swings and roundabouts - but I lean towards the latter for performance.

If [Param1] is indeed Null (because it hasn't been specified) then not including it at all in the query means there's nothing to fail to optimise. You therefore execute only the statement including criteria that is specified. Suppose Param1 isn't specified in the calling code but Param2 is (value 'Fred'). Then the above is built and executed as
WHERE FieldName2 = 'Fred'
as opposed to
WHERE (FieldName1 = Null OR Null Is Null) AND (FieldName2 = 'Fred' OR 'Fred' Is Null)

So although there are differences between database engines, generally speaking, good querying is universal.

I may have wandered from the exact topic of the question (which, by the way, I feel would have been better with an actual example query offered for consideration) but thought I'd carry on with the very important point Markus raised.

P.S. Sorry for the delay in posting long after this started - but I had to wait until some free time permitted me to say what I wanted to. :-)

Hi Leigh!

I always enjoy reading your comments. When posting in the Access zone, I'm careful to always show the example like this:

WHERE (Param1 = Field1 OR Param1 Is Null) AND (Param2 = Field2 OR Param2 Is Null)

A tiny mind bender for some readers ("I want the City to be equal to the parameter, not the other way around!"), but I'm sure you know the reason.

With the parameters on the left,  the clause will be viewable and editable in the Access query grid, see first sample below, while your version would be transformed into the second sample, mangling the SQL version also if you save...

Of course criteria using Like or Between/And are another matter.

I'm sure you know. Just saying!
(°v°)

Field:     Param1          Param1
Show:       [ ]             [ ]
Criteria:  Field1 Or Null  Field2 Or Null
 
----------
 
Field:     Field1    Field2    Param1    Param2
Show:       [x]       [x]       [ ]       [ ]
Criteria:  Param1    Param2
Or:                  Param2    Null
           Param1                        Null
                               Null      Null

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12: