I have a crosstab query in Access (2003 SP3). The result for the value field should meet these criteria when the query is run:
If null return 0
Arithmetic rounding (if .5 or greater round up, if less than .5 round down)
Return a number when exported to Excel (using DoCmd.OutputTo acOutputQuery)
The code below gives me the look I want but doesnt export as a number field into Excel:
40000lb units: Format(nz(Sum([10000lb units]/4),0),"0")
If I wrap Val or CInT around it I get the decimals.
Ive tried different combinations of Format, Val, CINT, NZ and IIF.
Any help would be greatly appreciated.