How to replicate Excels' numeric "Accounting" format in Access Rep

[JASelep]

How to replicate Excels' numeric "Accounting" format in Access Report

client want's Access report to output values in format identical to Excel's
numeric "Accounting" format
if I try to difine it as such it converts the 'Format' definition to >>
\acc"ou"n"ti"n\g
and strangely dosen't give the desired result

I'm using Office 2003 on Windows 2000

 

[Duane Hookom]

Have you tried Currency with 2 decimal places?

 

[JASelep]

yes

client wants "$" left aligned and amount right aligned
I could overlap 2 fileds 1 with just a left alligned $
and one with right aligned value
but then I'm very extremely likely to exheed the 1000 control limit in a
report section

 

[Duane Hookom]

I have trouble picturing a record that requires that many controls in a
single section of the report. I believe the limit is less than 1000.

However, you can use some code to accomplish the format display. Select each
control that you want to appear as Accounting format and enter "Accounting"
into the Tag property of the controls. Then add code to detail section On
Format event like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Accounting" Then
ctl.Format = "standard"
ctl.DecimalPlaces = 2
Me.CurrentX = ctl.Left
Me.CurrentY = ctl.Top
Me.Print "$"
End If
Next
End Sub

 

[JASelep]

well picture ~50rows with an 15month slide per row
include percentages and counts with row totals and labels
a few hidden controls per line to check integrity (making sure the discrete
percents and counts match-up) - I've bounced off of the limit a few times
having to chop lines/pages out to separate reports

Now each report setup actually only does one page of the final report for
each District, Sub-Region and Region, etc.

can I set this up as public for all reports within the db to use?

what differs in the define?

doesn't seem to like the useage of 'Me'

 

[John Spencer]

Here is a generic public function.

Public Sub sAddDollars(frmAny as Form)
Dim ctl As Control

For Each ctl In frmAny.Controls
If ctl.Tag = "Accounting" Then
ctl.Format = "standard"
ctl.DecimalPlaces = 2
Me.CurrentX = ctl.Left
Me.CurrentY = ctl.Top
Me.Print "$"
End If
Next ctl

End Sub

You can call it from the Details format event with

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
sAddDollars Me
End Sub


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[JASelep]

keep getting "invalid use of Me keyword" during compile

I have the public sub in Module1 of Modules

 

[Duane Hookom]

To me, ~50 rows would equate to ~50 records so I would try to reduce your
control number by dividing by ~50.

Did you try my code which is similar to John's but his is more generic? You
could try modify John's code like:

Public Sub sAddDollars(rptAny as Report)
Dim ctl As Control

For Each ctl In rptAny.Controls
If ctl.Tag = "Accounting" Then
ctl.Format = "standard"
ctl.DecimalPlaces = 2
rptAny.CurrentX = ctl.Left
rptAny.CurrentY = ctl.Top
rptAny.Print "$"
End If
Next ctl

End Sub

You can call it from the Details format event with

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
sAddDollars Me
End Sub

 

[JASelep]

yes I did try both codes - similar response
your latest code sample worked - I thiought I'd tried thart variation
yesterday in attempting to resolve the 'ME" error

IF I SHADE THE CONTROL, DOES THAT SUPERSCEDE THE "$" OR IS THE "$" HIDDEN
BEHIND THE NON-TRANSPARENT BACKGROUND? OR DOES THE SHADING NEED TO BE
DEFINED IN THE CTL.FORMAT ROUTINE?

 

[Duane Hookom]

Regarding the shading... do you want me to create a report, add the code, add
the shading, run the report, and reply back to the news group or should you
do it ;-)

 

[JASelep]

it was just that the $ wouldn't show when the control is shaded
so I somehow have to "send to back" the control and or "Bring to front" the
printing of the left aligned character

You have been most helpful

 

[Duane Hookom]

I wasn't sure if you had tried this before posting. Did you try move the code
to the On Print event?

 

[JASelep]

I hadn't, but have now - doesn't work

the $ gets hidden behind the opacity of the background color

 

[Duane Hookom]

I suppose you could use the line method to also draw a shaded box rather than
relying on the properties of the control.