- Jan 3, 2011
The query below feeds a report. I want to sort the report by "AOF" field. I keep running in to two problems. 1. I need to force nulls in the "AOF" field to 0 (they can't just display blank on the report) and 2. I need the AOF field to be in this format #,### (ie 1,234). I've tried every combination I can think of, if I use nz or iif to foce nulls to 0 on the query it changes to string so report can't sort on string. If I leave the query as is I can display "AOF" field in proper format and it sorts fine but I cannot force nulls to 0? Please, please, please help I've spent hours on this to no avail! Thank you.
SELECT qrysubqryAOFMargin.ClientNumber, qrysubqryAOFMargin.ClientName, qrysubqryAOFMargin.[IT/PA], qrysubqryAOFMargin.Product, qryAOFProductRollup.AOF, qrysubqryAOFMargin.SumOfTotalRevenue AS [Total Revenue], qrysubqryAOFMargin.SumOfTotalExpense AS [Total Expense], ([SumOfTotalRevenue]-[SumOfTotalExpense]) AS MarginDollar, IIf([SumOfTotalRevenue]=0,0,([SumOfTotalRevenue]-[SumOfTotalExpense])/[SumOfTotalRevenue]) AS MarginPercent FROM qrysubqryAOFMargin LEFT JOIN qryAOFProductRollup ON (qrysubqryAOFMargin.ClientNumber = qryAOFProductRollup.ClientNumber) AND (qrysubqryAOFMargin.[IT/PA] = qryAOFProductRollup.[IT/PA]) AND (qrysubqryAOFMargin.Product = qryAOFProductRollup.Product)