Format the results in the table.

I have a solution from a prior question of how to print out the formulas in a report. I've added ctl to the select statement. I  would like the ctl for TotalApprovedAmt, TotalAwardedAmt and TotalExpendedAmt to be formatted as currency. This db is just a practice db. The db I'm going to use the code on has all amounts for each control, but I can't attach that db due to the many tables and queries that run the report.
Vendor---Copy-2.accdb
Barbara69Asked:
Who is Participating?
 
FlysterConnect With a Mentor Commented:
Not sure if this is what you're looking for. For TotalAwardedAmt, change the Control Source from   =Sum([AwardedAmt])   to   =Format(Sum([AwardedAmt]),"Currency"). Change the other two ctl accordingly.

Flyster
0
 
Barbara69Author Commented:
I've attached a word document of the code used to produce the report, a pdf document of the actual report I'm working with, and an excel file of the field names, field control sources and the amounts.I hope this gives you a better idea. I'd like to have the amounts in currency format.
TblFormulasCode.docx
rptIDCProjected.pdf
tblFormulas.xlsx
0
 
Barbara69Author Commented:
Hi Flyster,

That works fine for a few text boxes, but the report I'll be working with has about 50 text boxes on it. Is there some way through code that all the text boxes can be changed? Please see the pdf document I attached in my earlier comment. I'd like to have the amounts in currency format in tblformulas.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Barbara69Author Commented:
Flyster,

I figured it out, but is there a way to have the currency values in just dollars with no cents?
0
 
Barbara69Author Commented:
Flyster,

I did a format in the below code to get the amounts in currency format for the tblformulas:

            DoCmd.RunSQL "Insert Into 2010IDCProjectedFormulas Values ('" & ctl.Name & "','" & ctl.ControlSource & "', '" & Format(ctl, "Currency") & "')"

I then changed the Windows regional settings to display no digits after the decimal.

Thank you for your assistance with this matter.
0
 
FlysterCommented:
Sorry, I haven't been available the last few days. I'm happy to see you got it working the way you wanted. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.