I now get an "Invalid Key Length" It goes away when I delete the IS NULL section. It's almost as if it can not test the data at that point in the query.

I perform a simple query like you suggested in advance of the main query. I have the "where id is null or str(id) + ttoc(effective_date, 1) in ..." and it doesn't work.

SELECT STR(a.id, 19) + MAX(TTOC(a.effective_date, 1)) max_dt ;
FROM names a INTO CURSOR viewNames_Max ;
GROUP BY a.id ;
ORDER BY 1

SELECT a.*, b.nameinfo...;
FROM ids a LEFT JOIN names b ;
ON a.id = b.id ;
WHERE (b.effective_date IS NULL OR STR(b.id, 19) + TTOC(b.effective_date, 1) IN ;
(SELECT max_dt FROM viewNames_Max))


b.effective_date IS NULL crashes the query. I wonder if the VFP order of operations comes into play here.

e.g. Any IF clause would normally process until the expression is true then allow entry into the section.

IF lFlag OR reccount(curTest) > 0
...
ENDIF

If lFlag is true then the RECCOUNT() would never execute because it doesn't need to.

In the case of the SQL though, I think it needs to execute all statements regardless of the "OR". So the "IS NULL" still gets processed in the STR() AND TTOC() which would fail... hmmm another test? brb

SELECT a.*, b.nameinfo...;
FROM ids a LEFT JOIN names b ;
ON a.id = b.id ;
WHERE (b.effective_date IS NULL OR STR(NVL(b.id, 0), 19) + TTOC(NVL(b.effective_date, DATETIME()), 1) IN ;
(SELECT max_dt FROM viewNames_Max))

This resulted in another "Invalid Key Length". This now doesn't make any sense! Not to mention as well, VFP kicks me out from time to time on this query.