Accept Column Names and AS names referenced later

[ijitnoin]

Help! SQL Server will not accept filename.SumOfInv_Total as a column name. Nor will it accept column names such as [1 - 30Days] as a valid column. You can not reference as column name use in 'AS' in sum, difference.

SELECT qryBatchMFGInvoice.MFG, qryBatchMFGInvoice.INVOICE, qryBatchMFGInvoice.MaxOfInvoiceDate AS InvoiceDate, qryBatchMFGInvoice.InvoiceStatus,
dbo.qryBatchMFGInvoice.SumOfINV_TOTAL AS [Total Invoice], dbo.qryBankMFGInvoice.SumOfINV_TOTAL AS [Total Paid],
qryBatchMFGInvoice.MaxOfInvoiceDate + 30 AS DueDate, tblManufacturerInfo.MFGNAME, CASE WHEN 0 < datediff(dd, GetDate(),
qryBatchMFGInvoice.MaxOfInvoiceDate) AND datediff(dd, GetDate(), qryBatchMFGInvoice.MaxOfInvoiceDate)
< 31 THEN [Total Invoice] - [Total Paid] ELSE 0 END AS [Current], CASE WHEN 0 < datediff(dd, GetDate(), [DueDate]) AND datediff(dd, GetDate(),
[DueDate]) < 31 THEN [Total Invoice] - [Total Paid] ELSE 0 END AS [1-30 DAYS], CASE WHEN 30 < datediff(dd, GetDate(), [DueDate]) AND datediff(dd,
GetDate(), [DueDate]) < 46 THEN [Total Invoice] - [Total Paid] ELSE 0 END AS [31-45 DAYS], CASE WHEN 45 < datediff(dd, GetDate(), [DueDate]) AND
datediff(dd, GetDate(), [DueDate]) < 61 THEN [Total Invoice] - [Total Paid] ELSE 0 END AS [46-60 DAYS], CASE WHEN 60 < datediff(dd, GetDate(),
[DueDate]) AND datediff(dd, GetDate(), [DueDate]) < 91 THEN [Total Invoice] - [Total Paid] ELSE 0 END AS [61-90 DAYS], CASE WHEN 90 < datediff(dd,
GetDate(), [DueDate]) THEN [Total Invoice] - [Total Paid] ELSE 0 END AS [Over 90 DAYS],
[1-30 DAYS] + [31-45 DAYS] + [46-60 DAYS] + [61-90 DAYS] + [Over 90 DAYS] AS [INVOICE TOTAL]
FROM qryBankMFGInvoice INNER JOIN
(tblManufacturerInfo INNER JOIN
qryBatchMFGInvoice ON tblManufacturerInfo.MFG = qryBatchMFGInvoice.MFG) ON (qryBankMFGInvoice.INVOICE = qryBatchMFGInvoice.INVOICE) AND
(qryBankMFGInvoice.MFG = qryBatchMFGInvoice.MFG)
WHERE (((qryBatchMFGInvoice.InvoiceStatus) = 'O'))
ORDER BY qryBatchMFGInvoice.INVOICE

 

[SQLSister]

That is correct. YOu cannot use the aliases within the same select (except in the order by clause I think). You need to repeat the formula you used to get the column or the original field name in the other locations of the query.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief