Roi

I have managed to sort the problem, by taking the join out.

I haven't fully checked this yet, but I think it's right. The join was to one other table, and in the SELECT I was filtering it to only pick up certain records from the 2nd table.

The 2nd table is only a lookup table (code,desc), and if the user doesn't pick an option from the lookup table, the SELECT won't pick it up, because it is filtering the whole view, when I only wanted it to filter on the 2nd table.

Here is the original code;

SELECT careplan.*,ccmslook.lk_desc;
FROM database!careplan LEFT OUTER JOIN database!ccmslook;
ON careplan.lk_code = ccmslook.lk_code;
WHERE ccmslook.lk_type = "AB"

Here is what I think it should have been;

SELECT careplan.*,ccmslook.lk_desc;
FROM database!careplan LEFT OUTER JOIN database!ccmslook;
ON careplan.lk_code = ccmslook.lk_code;
WHERE (ccmslook.lk_type = "AB" OR EMPTY(careplan.lk_code))

The latter code works fine.

So there it is.

Thanks for your help, Kev.