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

Re: Query works in Access but not VB6 80 80

try changing wild card for LIKE with '1%'

commented: Thanks, this did the trick : ) +0
Re: Query works in Access but not VB6 80 80

"LCD.*, LCS.*, LS.*, LMM.*"

try changing the * to the real field name / table name...

Re: Query works in Access but not VB6 80 80

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.

Re: Query works in Access but not VB6 80 80

Thanks, first answer did the trick!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.