Query expression using currency fields is not currency in the result

[louise]

Hello,


I have an expression in a query which involves going through assessment value field, covering multiple years, and selecting the most recent one. I get the expected result but it is not a currency field (e.g., I cannot sum the column in datasheet view -- but i can with the component parts).

I am hoping for some help, my several attempts have failed.
Below I tried using "FormatCurrency".
(I assumed an expression with all currency fields would result in the same. Is that false?)


AssmtMostRecentYr: FormatCurrency(IIf([Assessed-Value2025] Is Not Null,[Assessed-Value2025],IIf([Assessed-Value2024] Is Not Null,[Assessed-Value2024],IIf([Assessed-Value2023] Is Not Null,[Assessed-Value2023],IIf([Assessed-Value2022] Is Not Null,[Assessed-Value2022],IIf([Assessed-Value2021] Is Not Null,[Assessed-Value2021],0))))),2)

Thanks for any assistance!

[Gicu]

Try CCur(Iif([Assesed-.....) instead.

Cheers,

[June7]

FormatCurrency (as well as Format, FormatNumber) returns a string, not an actual number (or date) value. This can be an issue in subsequent calcs using that value.

Do formatting in report, not query.

Unless you are converting a string like "15.8332" to a number, I don't see any reason to use it. The result of CCur("15.8332") is 15.8332.

Consider:

Switch([Assessed-Value2025] Is Not Null,[Assessed-Value2025], [Assessed-Value2024] Is Not Null,[Assessed-Value2024], [Assessed-Value2023] Is Not Null,[Assessed-Value2023], [Assessed-Value2022] Is Not Null,[Assessed-Value2022], [Assessed-Value2021] Is Not Null,[Assessed-Value2021], True,0)

Could wrap that expression with Round() but be aware it uses even/odd banker's rounding.

[louise]

Thank you, Gicu.
That was precisely what I needed! I have never seen this function before and I'm going to remember it!

June7, thank you for your feedback too. I wanted to actually change/(maintain) the field as a currency type that I can download as a query and use in other calculations -- not just display it differently, so will go with CCur.

Thanks, both

[Gicu]

You're very welcome Louise!
Cheers,