The select statement contains a reserved keyword or argument name... problem

[hitesh102]

Hi

I have an issue. I am executing an sql query that contains IIF statement that runs fine in query editor in access but when I am running it in excel VBA, I am getting the error " The select statement contains a reserved keyword error.. Please help.
It usese the source date parameter.

Here is the query in VBA that is giving error

sSQL = "SELECT CompanyProfiles.CompanyCode, CompanyProfiles.CompanyRegisteredName, CompanyProfiles.CompanyTradingName, CompanyProfiles.ReportingSortOrder, qryCompanyAccountingPeriodsCurrent.FiscalYear, qryCompanyAccountingPeriodsCurrent.PeriodName, TransactionEntryHeader.DefaultBranchNumber, SupplierDetails.SupplierCode, SupplierDetails.Name, TransactionEntryHeader.DocumentNo, TransactionEntryHeader.DocumentDate, SupplierBranchDetails.DefaultPaymentTermsCode, PaymentTerms.PaymentTermsDescription, qryRptTransactionTotalsDateBased.Outstanding, TransactionEntryHeader.PaymentDueDate, [SourceDate] AS SourceDate, IIf(PaymentTerms.PaymentTermsCode='HELD',-1,IIf([PaymentDueDate]<[SourceDate],DateDiff('d',[paymentduedate],Now()),0)) AS OverDueDays, IIf([Overduedays]>=0 And [Overduedays]<29,[outstanding],0) AS Due, IIf([OverdueDays]>29 And [Overduedays]<60,[outstanding],0) AS 30Days, IIf([OverdueDays]>59 And [Overduedays]<90,[outstanding],0) AS 60Days, IIf([OverdueDays]>89,[outstanding],0) AS 90Days," & _
"IIf(PaymentTerms.PaymentTermsCode='HELD',[outstanding],0) AS Held, 0 AS YTDPurchases, '' AS LastPaid, PaymentTerms.PaymentTermsCode, BranchProfiles.CentralReportingNumber AS EBosStoreNumber" & _
"FROM (((((PaymentTerms INNER JOIN TransactionEntryHeader ON PaymentTerms.PaymentTermsCode = TransactionEntryHeader.PaymentTermsCode) INNER JOIN CompanyProfiles ON TransactionEntryHeader.CompanyCode = CompanyProfiles.CompanyCode) INNER JOIN qryCompanyAccountingPeriodsCurrent ON CompanyProfiles.CompanyCode = qryCompanyAccountingPeriodsCurrent.CompanyCode) INNER JOIN (SupplierDetails INNER JOIN SupplierBranchDetails ON SupplierDetails.SupplierCode = SupplierBranchDetails.SupplierCode) ON (TransactionEntryHeader.SupplierCode = SupplierDetails.SupplierCode) AND (CompanyProfiles.CompanyCode = SupplierBranchDetails.MasterBranchNumber)) INNER JOIN qryRptTransactionTotalsDateBased ON TransactionEntryHeader.TransactionID = qryRptTransactionTotalsDateBased.TransactionID) INNER JOIN BranchProfiles ON TransactionEntryHeader.DefaultBranchNumber = BranchProfiles.MasterBranchNumber" & _
" WHERE (((qryRptTransactionTotalsDateBased.Outstanding)<>0));"


and here is the query runing fine in access

PARAMETERS SourceDate DateTime; SELECT CompanyProfiles.CompanyCode, CompanyProfiles.CompanyRegisteredName, CompanyProfiles.CompanyTradingName, CompanyProfiles.ReportingSortOrder, qryCompanyAccountingPeriodsCurrent.FiscalYear, qryCompanyAccountingPeriodsCurrent.PeriodName, TransactionEntryHeader.DefaultBranchNumber, SupplierDetails.SupplierCode, SupplierDetails.Name, TransactionEntryHeader.DocumentNo, TransactionEntryHeader.DocumentDate, SupplierBranchDetails.DefaultPaymentTermsCode, PaymentTerms.PaymentTermsDescription, qryRptTransactionTotalsDateBased.Outstanding, TransactionEntryHeader.PaymentDueDate, [SourceDate] AS SourceDate, IIf(PaymentTerms.PaymentTermsCode='HELD',-1,IIf([PaymentDueDate]<[SourceDate],DateDiff('d',[paymentduedate],Now()),0)) AS OverDueDays, IIf([Overduedays]>=0 And [Overduedays]<29,[outstanding],0) AS Due, IIf([OverdueDays]>29 And [Overduedays]<60,[outstanding],0) AS 30Days, IIf([OverdueDays]>59 And [Overduedays]<90,[outstanding],0) AS 60Days, IIf([OverdueDays]>89,[outstanding],0) AS 90Days, IIf(PaymentTerms.PaymentTermsCode='HELD',[outstanding],0) AS Held, 0 AS YTDPurchases, "" AS LastPaid, PaymentTerms.PaymentTermsCode, BranchProfiles.CentralReportingNumber AS EBosStoreNumber FROM (((((PaymentTerms INNER JOIN TransactionEntryHeader ON PaymentTerms.PaymentTermsCode = TransactionEntryHeader.PaymentTermsCode) INNER JOIN CompanyProfiles ON TransactionEntryHeader.CompanyCode = CompanyProfiles.CompanyCode) INNER JOIN qryCompanyAccountingPeriodsCurrent ON CompanyProfiles.CompanyCode = qryCompanyAccountingPeriodsCurrent.CompanyCode) INNER JOIN (SupplierDetails INNER JOIN SupplierBranchDetails ON SupplierDetails.SupplierCode = SupplierBranchDetails.SupplierCode) ON (TransactionEntryHeader.SupplierCode = SupplierDetails.SupplierCode) AND (CompanyProfiles.CompanyCode = SupplierBranchDetails.MasterBranchNumber)) INNER JOIN qryRptTransactionTotalsDateBased ON TransactionEntryHeader.TransactionID = qryRptTransactionTotalsDateBased.TransactionID) INNER JOIN BranchProfiles ON TransactionEntryHeader.DefaultBranchNumber = BranchProfiles.MasterBranchNumber WHERE (((qryRptTransactionTotalsDateBased.Outstanding)<>0));

<tbody>
</tbody>

Thanks

 

[Jerry Sullivan]

Welcome to the Board,

Not sure that this is the cause of that error or the only problem but there's a stray quote mark in this expression after YTDPurchases,

"IIf(PaymentTerms.PaymentTermsCode='HELD',[outstanding],0) AS Held, 0 AS YTDPurchases, '' AS LastPaid, PaymentTerms.PaymentTermsCode, BranchProfiles.CentralReportingNumber AS EBosStoreNumber"

If that doesn't fix things, one technique to identify the syntax error by removing and adding fields in the SELECT expression until the error goes away. You can start by cutting the list in half then half again to quickly find the few fields that might be causing the error.