asked on

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

Barbara69

ASKER

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

ASKER CERTIFIED SOLUTION

Flyster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Barbara69

ASKER

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.

Barbara69

ASKER

Flyster,

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

Barbara69

ASKER

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.

Flyster

Sorry, I haven't been available the last few days. I'm happy to see you got it working the way you wanted. Thanks!