Solved

Access 2010 - report text box formatting

Posted on 2013-11-27
6
900 Views
Last Modified: 2013-12-04
I have a report that shows tax amounts in various currencies.

I want the tax amount to be formatted with square bracketing and the variable currency symbol shown like:
[$100.00]
[€250.00]
[£500.00]

At the report detail format event  I use vba:

Me!sInvoiceTax = "[" & GroupCurrencySymbol & Format(InvoiceTax, #,##.0.00) & "]"

At the group footer format event I tried to use vba:

Me!txtTotalInvoiceTax = "[" & GroupCurrencySymbol & Format(sum([InvoiceTax]), "#,###,##0.00") & "]"

but function sum is not recognised.

Instead I would prefer to have a bound text box in the group footer with control source:
sum([InvoiceTax]
and use the property sheet text box format property:
"[ $"#,##0.00"]"  but to change the $ to be my variable GroupCurrencySymbol

but I don't know how to set up the text box format property to do this.  How can I achieve the formatting I need, either via the property sheet or vba.
0
Comment
Question by:MonkeyPie
6 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Have you tried to use this expression as your format string:
"[ " & [GroupCurrencySymbol] & "#,##0.00" & "]"

Open in new window

0
 

Author Comment

by:MonkeyPie
Comment Utility
I added the following to the group footer format event:
 
  Me.txtSumNetInvoice.Format = "[ " & [GroupCurrencySymbol] & "#,##0.00" & "]"

Open in new window

It didn't work.  I just got 100.00 with no formatting at all.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
This should work:

Me!txtTotalInvoiceTax = Format(Sum([InvoiceTax]), "\[\" & [GroupCurrencySymbol] & "#,##0.00\]")

/gustav
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:MonkeyPie
Comment Utility
Sum is an SQL function, not VBA, so I can't use the code above.


In my initial post I wrote...
At the group footer format event I tried to use vba:

Me!txtTotalInvoiceTax = "[" & GroupCurrencySymbol & Format(sum([InvoiceTax]), "#,###,##0.00") & "]"

but function sum is not recognised.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
That's right. Try this instead with an additional textbox:

Me!txtSumInvoiceTax with this controlsource:
=Sum([InvoiceTax])

Me!txtTotalInvoiceTax = "[" & GroupCurrencySymbol & Format([txtSumInvoiceTax]), "#,###,##0.00") & "]"

/gustav
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 500 total points
Comment Utility
Sounds like the "sum" isn't working.
How about creating an invisible text box and add the sum function to it's control source.  Then, instead of sum(InvoiceTax) in your formula (above) you simply use the new text box you created.

Perhaps, trying this will discover why your sum doesn't work.

Scott Clark
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now