Formatting

  • Thread starter Thread starter BDavis
  • Start date Start date
B

BDavis

Hopefully this is an easy one:

I have two fields in a report [Debits], [Credits]

I'd like to format them such that, the first record has a
dollar sign and subsecquent records in that group do not.
Also, I like "0"'s to appear as a "-", similar to the
Accounting format in Excel.

Thanks in advance.
..
 
B,
Here's a workaround for the dollar sign question.
I'd create a column in the query behind the report like this...
DollarSign : "$"
Place that field just to the left of your Debits/Credits fields. Set
Hide Duplicates for DollarSign = yes.
Now, according to your grouping, the $ should only show up on the first
record of each group.

I would think you could use this technique (I didn't test it, but it
should fly) to solve your "-" problem... but I'm not sure about the "0"
problem.
 
BDavis said:
I have two fields in a report [Debits], [Credits]

I'd like to format them such that, the first record has a
dollar sign and subsecquent records in that group do not.
Also, I like "0"'s to appear as a "-", similar to the
Accounting format in Excel.
Click to expand...

It's easy to get the zeros to appear as anything you want by
using a custom format for the text box. E.g.
$#,##0.00;($#,##0.00);\-

The three parts are the formats for positive, negative and
zero values.

Only displaying the $ on the first detail in a group is
trickier. First, you have to have a way to determine that
it is the first or subsequent details. This can be done by
using a line counter text box. Create a text box named
txtLineCount, set its control source expression to =1 and
RunningSum property to Over Group.

Then use code in the detail section's Format event to modify
the dbit/credit text box's Format property:

If txtLineCount = 1 Then
txtdebit.Format = "$#,##0.00;($#,##0.00);\-"
Else
txtdebit.Format = "#,##0.00;(#,##0.00);\-"
End If
 
Back
Top