Hi All,
This is driving me made and I can't find the solution if you please advise?
I build the following query in access 2010
The query works as intended when I test it but the funny thing is Access then changes the sql which breaks it. Here is the amended SQL
Any help will be appreciated - I have no idea how to fix this!
This is driving me made and I can't find the solution if you please advise?
I build the following query in access 2010
Code:
SELECT TblReconciliations.RecMonthEndDate, TblSuppliers.SupplierName, TblReconciliations.RecUser, TblStatementInvoices.InvoiceNumber, TblStatementInvoices.InvoicePurchaseOrder, TblStatementInvoices.InvoiceDate, TblStatementInvoices.InvoiceValue, TblStatementInvoices.InvoiceTotalAccrued, TblStatementInvoices.InvoiceHospital, TblInvoiceStatus.StatusDesc, TblStatementInvoices.InvoiceOnNewDart, TblStatementInvoices.InvoiceOnOldDart
FROM TblInvoiceStatus INNER JOIN (TblSuppliers INNER JOIN (TblReconciliations INNER JOIN TblStatementInvoices ON TblReconciliations.RecID = TblStatementInvoices.InvoiceRecID) ON TblSuppliers.SupplierID = TblReconciliations.RecSupplier) ON TblInvoiceStatus.StatusID = TblStatementInvoices.InvoiceLiabilityStatus
WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect] Or (TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc] Or (TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblReconciliations.RecMonthEndSubmitted)=True));
Code:
SELECT TblReconciliations.RecMonthEndDate, TblSuppliers.SupplierName, TblReconciliations.RecUser, TblStatementInvoices.InvoiceNumber, TblStatementInvoices.InvoicePurchaseOrder, TblStatementInvoices.InvoiceDate, TblStatementInvoices.InvoiceValue, TblStatementInvoices.InvoiceTotalAccrued, TblStatementInvoices.InvoiceHospital, TblInvoiceStatus.StatusDesc, TblStatementInvoices.InvoiceOnNewDart, TblStatementInvoices.InvoiceOnOldDart
FROM TblInvoiceStatus INNER JOIN (TblSuppliers INNER JOIN (TblReconciliations INNER JOIN TblStatementInvoices ON TblReconciliations.RecID = TblStatementInvoices.InvoiceRecID) ON TblSuppliers.SupplierID = TblReconciliations.RecSupplier) ON TblInvoiceStatus.StatusID = TblStatementInvoices.InvoiceLiabilityStatus
WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) AND ((TblReconciliations.RecMonthEndSubmitted)=True)) OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) AND ((TblReconciliations.RecMonthEndSubmitted)=True) AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null)) OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblReconciliations.RecMonthEndSubmitted)=True) AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null)) OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblReconciliations.RecMonthEndSubmitted)=True) AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null And ([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null));