Why wont this sort???!!!!???

[automationamateur]

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)

 

[SydneyGeek]

Instead of AOF, trying
CDbl(NZ(AOF,0))

The CDbl should convert the 0 string back to a number. I used CDbl here in case you have decimals.

Denis

 

[automationamateur]

Thanks, I will give this a try, should I be putting this in the query or in the report? I'm thinking the query?

 

[SydneyGeek]

Put in the query; that way you can use it elsewhere, and check the result without having to run the report.
And I have been told by someone who should know, that pre-compiled queries are faster than SQL statements when used as the data source for reports.

Denis

 

[automationamateur]

Well, I tried the first bit below and it worked but removed commas so I wanted to format it so I tried the second bit below and all my nulls returned with "#Error". I still feel like I am running into the issue because I need it to be a number so i can sort, I need it to be formatted #,###. Am I missing something? I know I can add the AOF in the query/report twice and just show one of the fields in the report but I'm hoping to avoid that approach if possible. Thanks for the help thus far.

SAOF: SAOF: CDbl(NZ(AOF,0))

SAOF: CDbl(Format(Nz([AOF],0),"#,###"))

 

[automationamateur]

Learning can be so funny at times. Right after my last post I figured it out. I left Sydney Geeks suggestion in the query which does what it needs to (converts to # and forces 0 for nulls) and just applied the #,### formatting at the report level which apparently does not convert it back to a string (I was concerned it was going to do this). Now it displays and sorts everything perfectly. Thanks for the help!

SydneyGeek,

I am curious about the precompiled query remark. How would one precompile the query rather than the sql statement. Any links where I can do some reading up on this?

Thanks again.

 

[SydneyGeek]

Sorry, used a bit of shorthand there.
A 'precompiled' query as I referred to it is a saved query. Access does some background optimization.
A 'SQL Statement' is where your rowsource for the report is built using a SQL string, instead of a saved query.

The point I was trying to make, is that a saved query can give better performance on a big report than a SQL string used as the rowsource.

Denis

 

[automationamateur]

Gotcha, thanks.

 

[Christina594]

Learning can be so funny at times. Right after my last post I figured it out.