Calulated fields return #Error 1st time query is run

[Joe]

I have noticed that in some queries that have somewhat
complex calucated fields (especially ones with iif) the
calculated values show up as #Error the first time that I
open the query. If I close the query and open it again the
calucated field show the correct results. I believe that
this might have something to do with the queries having to
be occasionally recompiled. When I replace the inline SQL
calcuation with a VB function the #Errors don't appear but
it looks like the query takes much longer to execute. Any
recommendations.

 

[[MVP] S. Clark]

Any recommendations?
Don't use IIF() in your queries.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-