Formatting output text as currency

[Chris Freeman]

to all,
I have an output function to create a CSV text file. the output works fine.
But now, I want to caputre the total of the check amount field. This works
fine, but I can't get the field to convert to be represented as currency.
currently, I have:

Dim RID As Double
blah blah blah...

Print #fileout, rst.Fields![Check_Amount]
RID = RID + rst.Fields![Check Amount]

msgbox "You've exported n records for for X."

where X is displaying as 2127.3 instead of $ 2,127.30

I've tried:
RID = RID + val(rst.Fields![Check Amount]) - no change
RID = RID + CCur(rst.Fields![Check Amount]) - no change
RID = RID + CDBL(rst.Fields![Check Amount]) - no change

I could go back and grab the query data, but isn't there a way to get this
value to display in a double or currency format?

TIA

 

[Clifford Bass]

Hi Chris,

Use Format(RID, "Currency"), which will return a string using the
current regional settings of the computer.

Clifford Bass

 

[Chris Freeman]

Cliff,
Thanks, but still no go. I thought it was because the value was getting
converted to string in the query, as the Check Amount was being converted via
format, which changes the value to a string. Check Amount: format([Original
check Amount], "currency").

So I added the original field, Original Check Amount, and tried to format
that, but no success. Then I tried RDI = format(RID, "#,###.00") and it
worked. Then I noticed that it worked only because I misspelled the variable,
RDI instead of RID.

So it works now, I just have to remember not to use the same variable name
as the result of the formatting.

Thanks

--
Chris Freeman
IT Project Coordinator

Hi Chris,

Use Format(RID, "Currency"), which will return a string using the
current regional settings of the computer.

Clifford Bass

to all,
I have an output function to create a CSV text file. the output works fine.
But now, I want to caputre the total of the check amount field. This works
fine, but I can't get the field to convert to be represented as currency.
currently, I have:

Dim RID As Double
blah blah blah...

Print #fileout, rst.Fields![Check_Amount]
RID = RID + rst.Fields![Check Amount]

msgbox "You've exported n records for for X."

where X is displaying as 2127.3 instead of $ 2,127.30

I've tried:
RID = RID + val(rst.Fields![Check Amount]) - no change
RID = RID + CCur(rst.Fields![Check Amount]) - no change
RID = RID + CDBL(rst.Fields![Check Amount]) - no change

I could go back and grab the query data, but isn't there a way to get this
value to display in a double or currency format?

TIA

 

[Clifford Bass]

Hi Chris,

Yes, you would not be able to assign the results of the Format()
function, a string, to a Double variable.

Also, I should explain why none of your attempts worked. Take this one:

RID = RID + CCur(rst.Fields![Check Amount])

It takes the Check Amount field and makes sure it is a currency value.
Then it takes the Double, RID, and adds the currency amount to it. This does
an implicit conversion of the currency amount to a Double. When numbers are
converted implicitly they go from the more specific to the more general type.
You then assign the result to a Double. So your value will be a Double (by
definition). So, when you display it, it will be displayed as a Double.

However, you do not need to assign the resulte to a variable. You can
use it directly. Instead of MsgBox RID, which will display 2127.3, use
MsgBox Format(RID, "Currency") or MsgBox Format(RID, "$#,##0.00"), which will
show $2,127.30. Hopefully that clarifies what I was suggesting.

Finally, a good-practice piece of advice. It is wise to add to the top
of all of your modules a "Option Explicit" line, just above or below the
"Option Compare ..." line. This will force you to declare all of your
variables, which helps you to discover when you have mistyped a variable type
and also it helps to make sure you are using the right variable types in that
it will warn in you try to do an illegal assignment. You can have Access
automatically add this line by going to the Tools menu in the VBA Editor and
choosing Option. On the Editor tab check the Require Variable Declaration.

Hope that is helpful,

Clifford Bass