Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Format the results in the table.

Posted on 2012-08-20
6
Medium Priority
?
290 Views
Last Modified: 2012-08-22
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
0
Comment
Question by:Barbara69
  • 4
  • 2
6 Comments
 

Author Comment

by:Barbara69
ID: 38314901
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
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 38314922
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
 

Author Comment

by:Barbara69
ID: 38318663
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Barbara69
ID: 38318682
Flyster,

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

Author Comment

by:Barbara69
ID: 38318727
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
 
LVL 22

Expert Comment

by:Flyster
ID: 38321139
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question