How to format recordset field as currency

[kdk13]

On a form, I have a button that generates an email. Within the email text, I am putting a dollar amount. This dollar amount comes from a recordset that I retrieve from the tables. The field in the table is formatted as Currency. The field on the form is formatted as Currency. The record source has it formatted as Currency. I tried just putting the recordset field in the text. I tried doing a CCur around the recordset field. I also tried to dim a Currency field and set it to the recordset field and putting the variable in the text. But it still shows as just a number. I want to see $20,088.00 in my email text, but I get 20088. Or $432.20 shows as 432.2. I can put the $ in my text, but not the comma, period and two decimals without a lot more coding.

I've tried various key word searches through tek-tips. Microsoft KB appears to be down right now. My project lead was stumped, too.

Anyone have any ideas? This can't be that hard, can it?

kdk13

 

[yamafopa]

If you format the field as currency in the recordset definition, you should get a good result...

strSql = "SELECT Format([tblDisk].[test], 'Currency') AS CurTest, tblDisk.disk_no " _
& "FROM tblDisk WHERE (((tblDisk.test)>0));"

hth
Chris

 

[kdk13]

Thanks so much for the help. That worked great!

kdk13

 

[nimarii]

Hello -

I'm having the same problem, but can't seem to get it to work. Here's my sql statment:

strSQL = "SELECT paymentamount FROM t_paymentamounts WHERE callingplan = '" & LDplan & "'"

I need to have paymentamount as a currency field, but the whole code errors out when I try to format the sql statement to currency.

I'd greatly appreciate any help!
Thanks,
Ellen