Access 2007 Reports - Can I Conditionally Print Additional Line in Detail Section? (1 Viewer)

[cblaine99]

My question concerns reports in Access 2007.

"rptInvoice" prints an invoice and multiple line items.
If a line item is for $1,000 or more, I want to print another line
immediately below it which shows a credit of $50. If the line item
is for less than $1,000 this additional line should not appear.

So imagine an invoice with two line items, one for $1,000 and one for $500.
I would like the Detail section of the report to look like this:

Part 123 - Big Machine    $1,000.00
Credit                      -$50.00

Part 124 - Small Machine    $500.00

The source table for the report has two records. There is no record
for the credit. I want the report to conditionally print this additional
line when an item is for $1,000 or more.

Is there a way to design the report to achieve this? Or is there a way to do this
using VBA?

 

[strive4peace]

hi cblaine,

an easy way to do this is:

make 2 calculated fields in the record source of the report, something like this:
ExtraLineLabel: iif( nz([amount_fieldname],0) >=1000, "Credit", Null)
ExtraLineAmount: iif( nz([amount_fieldname],0) >=1000, ccur(-50), Null)

where:
amount_fieldname is the name of your field with the amount

1. Add two controls on your report for these 2 new fields
2. once you get them where you want them to be, set these properties:
CanGrow = Yes
Height = 0.01 (or something very small)
3. close up the extra space

if the controls have a value, the line will get tall enough to show it

No VBA code needed

 

[cblaine99]

hi cblaine,

an easy way to do this is:

make 2 calculated fields in the record source of the report, something like this:
ExtraLineLabel: iif( nz([amount_fieldname],0) >=1000, "Credit", Null)
ExtraLineAmount: iif( nz([amount_fieldname],0) >=1000, ccur(-50), Null)

where:
amount_fieldname is the name of your field with the amount

1. Add two controls on your report for these 2 new fields
2. once you get them where you want them to be, set these properties:
CanGrow = Yes
Height = 0.01 (or something very small)
3. close up the extra space

if the controls have a value, the line will get tall enough to show it

No VBA code needed

Thank you! I will be trying this later today. It sounds like a great solution.
I assume the two new controls will be on a line right below the regular line. Is that correct?
Thanks again.

 

[strive4peace]

hi cblaine,

You're welcome Yes! Make sure there isn't extra space above it -- and set Left/right/Top/Bottom Padding to be 0.

 

[arnelgp]

another way to do it is put 2 unbound textbox on the report. 1st txtbx control source: ="Credit", the other =-50.
on design view of the report, select the 2 unbound textbox. using conditional format on the ribbon, Expression Is, [theItemAmountField]>=1000, select White foregeound of the font.

 

[strive4peace]

Arne, the problem with that is they would take space.

 

[arnelgp]

yes it would ms. crystal.
btw, ops mentioned there is no record on the credit table? is this some sort of "cheat". he should properly add the -50 "contra" charge to this table.

 

[strive4peace]

btw, ops mentioned there is no record on the credit table? is this some sort of "cheat". he should properly add the -50 "contra" charge to this table.

no, not if in the SQL for the Record source.
SELECT fieldname, iif( nz([amount_fieldname],0) >=1000, "Credit", Null) as ExtraLineLabel ...

 

[arnelgp]

what I mean't by "cheat" is if some auditor view his report (with Credit), and look for the $-50 on the Credit table on same Particular, he will not find it, and a lot of explaining must be done.

 

[Minty]

I agree with @arnelgp, I think this should be part of the invoice detail records or recorded and shown in a discount column, otherwise, it wouldn't show in the history of the records?

 

[strive4peace]

perhaps there is a different system for the official accounting

 

[Pat Hartman]

I agree that just putting this on the invoice is not the proper procedure, Either add the records to the invoice or add a second discount field. This lets you include the amounts in the calculated totals and it gives you an audit trail as arne suggested.

What if your rules for calculating this credit change? How would you be able to go back and reprint an invoice exactly as it was originally printed.