Sorting of Calculated field - sum a calculated field

[tbonehwd]

If in an Access Report you can't sort on a calculated field and in a query you can't sum fields that have already been calculated then how would one do this?

In my final report I want the NET_DOLLARS sorted Descending
based on the select below I am getting DOLL_PUR and DOLL_RET which DOLL_PUR - DOLL_RET = NET_DOLLARS but I can't do that calculation in this select I have discovered and once I bring this into the report I can do a calculation to get my NET_DOLLARS but I can't sort on it.

Any suggestions?

"SELECT PROOLN_M.ITM_NUM, PROOLN_M.DSC_001, PROOLN_M.DSC_003, CDSITM_M.CPR_YER " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_PUR " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_RET  " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_PUR  " & _
         ", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_RET  " & _
         "FROM (PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM=CDSITM_M.ITM_NUM " & _
         "WHERE PROOLN_M.OLN_STA = 'Z' AND CDSITM_M.ITM_SAC = 'BCS'" & _
         "AND PROOLN_M.SHP_CTM ='000000601248' " & _
         "AND PROORD_M.ACT_DTE  >=#06/30/2005# "& _
         "AND PROORD_M.ACT_DTE <=#07/31/2005#  " & _
         "AND PROOLN_M.ITM_NUM ='0006' " & _
         "GROUP BY PROOLN_M.ITM_NUM, PROOLN_M.DSC_001, PROOLN_M.DSC_003, CDSITM_M.CPR_YER ;"

 

[PHV]

I can't do that calculation in this select
Have you tried something like this ?
...
", Sum(PROOLN_M.ITM_NET * IIf(PROOLN_M.ORD_NUM<'90000000',1,-1)) AS NET_DOLLARS " & _
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886