I have used SQL Pass Thru for years but using only one table. My requirement is to create complex SQL statements and I hope that SQL Pass Thru is up to the task. Is there a limit of some kind that I should be aware of? It seems that adding the AND statement causes a problem. It does not matter what I set the AND statement and field to either.

VFP 6.0 and SQL Server 2000 are in use with Windows 2000.

Basic Examples:

This works:

SQLEXEC( tConnection, 'SELECT Tally.ReworkNumber, Tally.Problem, TallyDetail.Kanban, TallyDetail.PartNumber, Tally.PartName, Tally.SupplierName ;						
                          FROM Tally, TallyDetail ;
                          WHERE Tally.ReworkNumber = TallyDetail.ReworkNumber ','cuWeekly')

This does not work:

Local ldEndDate
ldEndDate =  "09/22/2004"

SQLEXEC(tConnection, 'SELECT Tally.ReworkNumber, Tally.Problem, TallyDetail.Kanban, TallyDetail.PartNumber, Tally.PartName, Tally.SupplierName ;						
				FROM Tally, TallyDetail ;
				WHERE Tally.ReworkNumber = TallyDetail.ReworkNumber ;
				AND Tally.DateCreated = ?ldEndDate','cuWeekly')

Here is one of the queries I have to create and this is taken from Access:

SELECT DISTINCTROW [qryQuantityReworked_s].[ReworkNumber], 
 [qryQuantityReworked_s].[Code], Sum([qryQuantityOK_s].[Monday]) AS OKMonday, Sum([qryQuantityReworked_s].[Monday]) AS ReworkedMonday, Sum([qryQuantityReturned_s].[Monday]) AS ReturnedMonday, Sum([qryQuantityOK_s].[Tuesday]) AS OKTuesday, Sum([qryQuantityOK_s].[Wednesday]) AS OKWednesday, Sum([qryQuantityOK_s].[Friday]) AS OKFriday, Sum([qryQuantityOK_s].[Saturday]) AS OKSaturday, Sum([qryQuantityReworked_s].[Tuesday]) AS ReworkedTuesday, Sum([qryQuantityReworked_s].[Wednesday]) AS ReworkedWednesday, Sum([qryQuantityReworked_s].[Friday]) AS ReworkedFriday, Sum([qryQuantityReturned_s].[Tuesday]) AS ReturnedTuesday, Sum([qryQuantityReturned_s].[Wednesday]) AS ReturnedWednesday, Sum([qryQuantityReturned_s].[Friday]) AS ReturnedFriday, Sum([qryQuantityReturned_s].[Saturday]) AS ReturnedSaturday, Sum([qryQuantityReworked_s].[Saturday]) AS ReworkedSaturday, [Rework].[SupplierName] AS Supplier, [Rework].[PartName], [Rework].[ReworkNumber], [LineCall].[QCEContact], [LineCall].[Packaging], [Rework].[Responsibility], Sum([qryQuantityOK_s].[Thursday]) AS OKThursday, Sum([qryQuantityReworked_s].[Thursday]) AS ReworkedThursday, Sum([qryQuantityReturned_s].[Thursday]) AS ReturnedThursday, [Rework].[Style] AS Model, [Rework].[RIDate] AS RIDate, [Rework].[Status] AS Status, [Rework].[DateClosed] AS ActualStopDate, [qryCumulativeTotal2_s].[SumOfQuantityReworked], [qryCumulativeTotal2_s].[SumOfQuantityReturned], [qryCumulativeTotal2_s].[SumOfTotalOK], [Rework].[ReworkLocation], [TallyDetail].[PartNumber] INTO tblWeeklyReport2_s
FROM ((TallyDetail INNER JOIN Rework ON 
[TallyDetail].[ReworkNumber]=[Rework].[ReworkNumber]) INNER JOIN (((qryQuantityOK_s INNER JOIN qryQuantityReworked_s ON ([qryQuantityOK_s].[Code]=[qryQuantityReworked_s].[Code]) AND ([qryQuantityOK_s].[REWORKNumber]=[qryQuantityReworked_s].[ReworkNumber])) INNER JOIN qryQuantityReturned_s ON ([qryQuantityReworked_s].[Code]=[qryQuantityReturned_s].[Code]) AND ([qryQuantityReworked_s].[ReworkNumber]=[qryQuantityReturned_s].[ReworkNumber])) INNER JOIN qryCumulativeTotal2_s ON ([qryQuantityReturned_s].[Code]=[qryCumulativeTotal2_s].[Kanban]) AND ([qryQuantityReturned_s].[ReworkNumber]=[qryCumulativeTotal2_s].[ReworkNumber])) ON ([Rework].[ReworkNumber]=[qryQuantityOK_s].[REWORKNumber]) AND ([TallyDetail].[ReworkNumber]=[qryCumulativeTotal2_s].[ReworkNumber])) INNER JOIN LineCall ON [TallyDetail].[ReworkNumber]=[LineCall].[ReworkNumber]
GROUP BY [qryQuantityReworked_s].[ReworkNumber], 
[qryQuantityReworked_s].[Code], [Rework].[SupplierName], [Rework].[PartName], [Rework].[ReworkNumber], [LineCall].[QCEContact], [LineCall].[Packaging], [Rework].[Responsibility], [Rework].[Style], [Rework].[RIDate], [Rework].[Status], [Rework].[DateClosed], [qryCumulativeTotal2_s].[SumOfQuantityReworked], [qryCumulativeTotal2_s].[SumOfQuantityReturned], [qryCumulativeTotal2_s].[SumOfTotalOK], [Rework].[ReworkLocation], [TallyDetail].[PartNumber];

Should I punt?

Excuse the formatting. This formatting seems to have a mind of it's own! :)

Any suggestions will be appreciated.

Tom