I have a switch query which I need to format into currancy: The query works fine until I included the format portion.

Variance: Format(Switch([EXPENDED]>[CURRENT BUDGET],([COMMIT BAL]+[EXPENDED]-[CURRENT BUDGET]),([COMMIT BAL]+[EXPENDED]-[CURRENT BUDGET])=0,0,True,([COMMIT BAL]+[EXPENDED]-[CURRENT BUDGET],"$#,##0")))

Hi,

Can you post the code that used to work just for comparison? Not sure if you added more than just the Format() function in there.

You probably have the closing parens in the wrong places. Try:

...True, ([COMMIT BAL]+[EXPENDED]-[CURRENT BUDGET])), "Currency")

Just my 2 cents...

Here is the SQL view:

SELECT Qry_AAC_Rollup_Sum.PROJ_ROLLUP_ID, Qry_AAC_Rollup_Sum.PROJ_ID, Qry_AAC_Rollup_Sum.PROJ_FINANCIAL_ID, Qry_AAC_Rollup_Sum.BUCKET_ID, Qry_AAC_Rollup_Sum.PROJ_NAME, Qry_AAC_Rollup_Sum.[CUR BUDGET] AS [CURRENT BUDGET], Qry_AAC_Rollup_Sum.[COMMIT BALANCE] AS [COMMIT BAL], Qry_AAC_Rollup_Sum.EXPENDED, IIf(IsNull([ESTIMATED_COST]),0,[ESTIMATED_COST]) AS [ESTIMATED COST], IIf(IsNull([COMMIT BAL]+[EXPENDED]+[ESTIMATED COST]),0,([COMMIT BAL]+[EXPENDED]+[ESTIMATED COST])) AS EAC, IIf(IsNull([current budget]=0),0,([CURRENT BUDGET]-([commit bal]+[expended]+[ESTIMATED COST]))) AS DELTA, Switch([EXPENDED]>[CURRENT BUDGET],([COMMIT BAL]+[EXPENDED]-[CURRENT BUDGET]),([COMMIT BAL]+[EXPENDED]-[CURRENT BUDGET])=0,0,True,([COMMIT BAL]+[EXPENDED]-[CURRENT BUDGET])) AS VARIANCE
FROM Qry_AAC_Rollup_Sum LEFT JOIN Qry_AAC_Rollup_ISSUE ON (Qry_AAC_Rollup_Sum.PROJ_FINANCIAL_ID = Qry_AAC_Rollup_ISSUE.PROJ_FINANCIAL_ID) AND (Qry_AAC_Rollup_Sum.BUCKET_ID = Qry_AAC_Rollup_ISSUE.BUCKET_ID)
ORDER BY Qry_AAC_Rollup_Sum.PROJ_ROLLUP_ID, Qry_AAC_Rollup_Sum.PROJ_ID, Qry_AAC_Rollup_Sum.PROJ_FINANCIAL_ID, Qry_AAC_Rollup_Sum.BUCKET_ID;

It worked, thanks for your help

Hi,




Glad to hear you got it to work. Good luck with your project.