>I have a view that queries on 3 different fields depending on a property on the form.
>
>Here is what I use:
>
>SELECT Careplan.*,
>Ccmslook.lk_desc
>FROM careplan
>LEFT OUTER JOIN ccmslook
>ON Careplan.lk_code = Ccmslook.lk_code
>AND Ccmslook.lk_type = "AB"
>WHERE Careplan.cl_ref = ?thisForm.lcCl_Ref
>AND Careplan.ia_ref = IIF(?thisForm.cAssessment="I",?thisForm.lcIa_Ref,"")
>AND Careplan.co_ref = IIF(?thisForm.cAssessment="C",?thisForm.lcCo_Ref,"") AND Careplan.ci_ref = IIF(?thisForm.cAssessment="P",?thisForm.lcCi_Ref,"") ORDER BY Careplan.cp_type
>
>
>Can anyone tell me if the above is visable, and if not, what is the way I should be doing this.
>
>Thanks
>Kev

Should AND Ccmslook.lk_type = "AB" really be part of the JOIN condition? This looks more like it belongs in the filter [WHERE] clause. Personally, I do not like the use of object property references in SQL. I first store these values to variables and use the variables in the SQL. If you used variables, you could then remove all the IIFs from the SQL which would go a long way to optimization.