DOH!

Thanks Sergey. You'll notice I DO in the lower SUM( IIF( BETWEEN( ET.date,... clauses. I'd forgotten this, and I lifted the IIF()s from a WHERE clause elsewhere and didn't consider that. :-)

Terry

>You cannot use BETWEEN operator inside IIF(). You have to use BETWEEN() function.
>
>>
>>This is one of those look-over-my-shoulder jobs:
>>
>>I added the complex nested IIF() to this SQL. Intellisense matches up all my parenth. and AFAICS the IIF()s are pukka, but the compiler keeps screaming "Function Name is missing) err mess" every time I try to leave the method.
>>
>>Maybe the SQL has given up on it?
>>
>>Can anyone see the flaw, cos I can't see the brackets for the parenth any more?
>>
>>Essentially it's (should be) saying "If there is a matching operator/route combo, in the RteFam table, well if it's dates are current with the ET rec's date, use its SERVTYPE field - else put a '#' (to denote out-of-date). If there is no match, put a '?' to denote 'not known'"
>>
>>Have that on my desk by the (my) tommorrow morning would you? :-)
>>
><snip>