Can anyone please tell me why this returns records in Access SQL pane but not from VB6 using ADO?

SELECT LCD.*, LCS.*, LS.*, LMM.*, LPS.si_ElementNumber FROM ((TableLOANContractualDynamic LCD INNER JOIN TableLOANLoanStatic LS ON (LCD.sv_LoanNumber = LS.sv_LoanNumber) AND (LCD.sv_CoNumber = LS.sv_CoNumber)) INNER JOIN TableLOANLoanContractualStatic LCS ON (LCD.sv_LoanNumber = LCS.sv_LoanNumber) AND (LCD.sv_CoNumber = LCS.sv_CoNumber)) INNER JOIN TableLOANLoanMasterMisc LMM ON (LCD.sv_LoanNumber = LMM.sv_LoanNumber) AND (LCD.sv_CoNumber = LMM.sv_CoNumber) WHERE LS.sv_CoNumber = '01' AND LCD.sd_NextDueDate BETWEEN #1/1/1960# AND #12/31/2059# AND LS.sv_LoanName BETWEEN ' ' AND 'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' AND LCS.sf_PaymentAmount BETWEEN - 9999999999.00 AND 9999999999.00 AND LCD.sd_LastTransactionDate BETWEEN #1/1/1960# AND #12/31/2059# AND LCD.sf_PrincipalBalance BETWEEN - 9999999999.00 AND 9999999999.00 AND sv_SystemFlags LIKE '1###############' ORDER BY sd_NextDueDate, sv_LoanName, sf_PaymentAmount

I have picked up a few mistakes in your query. As Jhai and dsp said, that will solve 2 problems. Your date values are also incorrect. Use -

... WHERE DateValue(YourStartDate) AND DateValue(YourEndDate)

I found it much easier to use a SQL writer application to solve my more involved queries. Try it as well. It will take you to the exact spot where there is an error in your SQL.

This question has already been answered. Start a new discussion instead.