>Hi All,
>This works in SQL Server, however VFP cannot handle the complexity. :( I'm not sure how to rewrite it.

>SELECT S.Clm_Id, S.Svc_Dt ;
>	FROM Mc_MdSvc S, Mc_MdClm C ;
>	WHERE C.Adjt_Stat = "Z" ;
>	AND C.Clm_Id = S.Clm_Id ;
>	AND NOT EXISTS (SELECT * FROM Mc_PlnHs P ;
>				WHERE P.Mem_Num = C.Mem_Num ;
>				AND P.Active_Ind = .T. ;
>				AND P.Pln_Id = C.Pln_Id ;
>				AND P.Ins_Id_Num = C.Ins_Id_Num ;
>				AND P.Eff_From <= S.Svc_Dt ;
>				AND P.Eff_Thru >= S.Svc_Dt)
>

SWAG, try:

>SELECT S.Clm_Id, S.Svc_Dt ;
	FROM Mc_MdSvc S, Mc_MdClm C ;
	WHERE C.Adjt_Stat = "Z" ;
	AND C.Clm_Id = S.Clm_Id ;
	AND NOT EXISTS (SELECT * FROM Mc_PlnHs P ;
				WHERE P.Mem_Num = C.Mem_Num ;
				AND <b>P.Active_Ind</b> ;
				AND P.Pln_Id = C.Pln_Id ;
				AND P.Ins_Id_Num = C.Ins_Id_Num ;
				AND <b>S.Svc_Date BETWEEN P.Eff_From P.Eff_Thru</b> )

If it's a matter of receiving a SQL Statement Too Long or Insufficient Stack Space error, try increasing the complexity value using SYS(3055), which controls the evaluation of WHERE and FOR clauses - the default value is 320, and can be set to a value between 320 and 2048 (bigger means a more complex query can be handled) for example:

=SYS(3055,1024)


>What it chokes on is the last 2 lines of the subquery. It only can handle equality. Any Ideas?