Print only specific lines of a report. Help please!

[Sausagefingers]

Hi,
I would like to know if it's possible (if so, how) to restrict the data produced by a report to records that have a (currency) value greater than £0.00.

Currently, the report is based on a query which does most of the 'donkey work'...creating subtotals etc. The report itself is calculating applied tax and performs a running total/grand total for invoice purposes.

The specific problem I want to solve is NOT having zero value transactions cluttering the invoice.

At the moment, my report is set up so that every possible transaction has its own report header. Is it possible simply to hide the header for printing purposes IF the subtotal textbox for that transaction/header is "£0.00"?

Hope this makes sense...hope you can help!

 

[allan57]

Put the criteria into your query.

 

[Sausagefingers]

Thanks for the response. I was pre-empting that this would be the answer to my problem. I'm not sure though, how this expression/criteria should be formulated.

For example, lets say a record consists of 5 separate charges in the same query.

That might translate into the following:

Units distributed to X1
Units distributed to X2
Units distributed to X3
Units distributed to X4
Units distributed to X5

Each distribution point 1 through 5 has a separate charge which is number of units distributed * cost per unit to distribute. This is calculated in the same query as an expression (X1Total, X2Total etc).

If 2 units were distributed to X1 but zero units were distributed to X2, X3, X4, X5, I wouldn't want zero cost transactions to show up on the printed report. At the momont they are. Preferrably, I would like only the X1 transaction to appear in the report.

Using simple criteria such as '>0' for the appropriate query fields hides the entire record. Therefore X1 wouldn't get charged for the 2 units distributed (in the above example).

Can you suggest the correct criteria or type of expression I would need to use in order to achieve this??

Thanks In Advance.

 

[allan57]

Another method you could try is under the details section of your report, make the relevant fields invisible if they have a value of zero ie

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

if X1Total=0 then X1Total.Visible=false else X1Total.visible=true
if X2Total=0 then X2Total.Visible=false else X2Total.visible=true
if X3Total=0 then X3Total.Visible=false else X3Total.visible=true
etc

End Sub

 

[schung]

I have similar issue/problem

I have some text fields which needs to be hidden (if no data). Will it work same way?

 

[allan57]

Schung

if nz(TextField, "")="" then TextField.Visible=false else TextField.visible=true

or

if isnull(TextField)= true then
TextField.Visible=false
else
TextField.Visible=true
end if

 

[schung]

Thanks

Thanks, Allan.

I will try it out.

 

[Sausagefingers]

Hi allan,
thanks for this. This works as expected although I have had to extend the same logic to make all fields on the same line (rather than just the single field) not show.

It appears the problem lies in the way I have structured the table
Apparantly the table upon which all this rests is not normalized so Iv'e created a rod for my own back!

I'm not inclined to restructure the table as yet...I don't want to break anything else. Your solution has come in handy!

Thanks