Change Currency Format in MS Access Query

[SeaMcD1]

I have a MS Access query with a total value sum field (tlval) that returns dollar values in varying string sizes, i.e. billions, millions, thousands, etc. How can I reduce the length of the string? If the dollar value is in Billions or Thousands, I want to both round down and show these amounts in a Millions format. Examples: for $1,235,856,890, I need to show as $1236M. And $8,940 shown as $.009M. All amounts less than a thousand dollars I want to keep as-is.

 

[PHV]

Have a look at the IIf function.

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

 

[Steve3110]

Hi

Could you put the tlval expression to something like the following:

iif(sum([valuefield])>1000, "$" & str(format([valuefield]/ 1000000, "0.00")) & "M", "$" & str(format([valuefield], "0.00")))

Obviously [ValueField] is the field that you are needing to sum

Steve

 

[SeaMcD1]

I can't figure-out how to incorporate the expression you suggested into my SQL statement? Whenever I try changing "valuefield" to either the field being summed (OBL) or the new field (TLVAL) in the query, I get syntax or other errors, etc. Below is my original SQL - can you please help?


SELECT KRTMSTR.CUSTDOD, Sum(KRTMSTR.OBL) AS [TLVAL]
FROM CMODOAC INNER JOIN KRTMSTR ON CMODOAC.DODAC = KRTMSTR.CMODOD
GROUP BY KRTMSTR.CUSTDOD;

 

[Steve3110]

Try the following:

SELECT KRTMSTR.CUSTDOD, iif(sum(KRTMSTR.OBL)>1000, "$" & str(format(KRTMSTR.OBL / 1000000, "0.00")) & "M", "$" & str(format(KRTMSTR.OBL, "0.00"))) AS [TLVAL]
FROM CMODOAC INNER JOIN KRTMSTR ON CMODOAC.DODAC = KRTMSTR.CMODOD
GROUP BY KRTMSTR.CUSTDOD;

P.S. Sorry if it doesnt work, its a bit hard trying to imagine how the query works in my head

 

[SeaMcD1]

First, I really appreciate all your help so far. I may be over-complicating this for my purposes. I've decided not to try and do this as part of this query. When I could not get your SQL statement to work, I tried disecting it to understand the structure, etc. Admittedly, I'm over my head. Instead, I want to just stick with the table (KRTMSTR) I'm using for my source data. I think part of the problem I'm having is trying to manipulate aggregate data.

So, if I could please indulge your assistance for the last time: This table has a contract obligation field (OBL) of widely differing dollar amounts. I need to drop all decimals (cents) and abbreviate to $K, $M, and in a few cases $B. I want the dollar amounts to appear as follows with no cents:

- Less than $50,000: no change
- Between $50,000 and $499,000: $.050K to $499K
- Between $500,000 and $499,000,000: $.500M to $499M
- Greater than $500,000,000: $.500B

Thanks