how to filter only non-zeroes on the report

[associates]

Hi,

just wonder if anyone might be able to help me here. I have a report that displays a set of records containing years, values, and total as follows

ProjectName Department 2006/7 2007/8 2008/9
RF transmitter Electronic 20 20 30
Banking Commerce 0 0 0
Grouping C-programming 50 0 0

What i'd like to do here is to show only projects that has non-zeroes values in the years as follows

RF transmitter Electronic 20 20 30
Grouping C-programming 50 0 0

it doesn't return record of Banking because of zeros across the years.

I know that we can do this thru query BUT for some reason, i'm not allowed to touch the query. Hence, i have to do the hard way namely to play with the report so that it would do what we want it to.

ProjectName, Department, 2006/7, 2007/8, 2008/9 are fields in the table.

what i have done so far is to set the filter on to YES. and in the Detail_Format event, i have the following code

...
Cancel = (Nz(["'" & myYear1 & "'"], 0) + Nz(["'" & myYear2 & "'"], 0) + Nz(["'" & myYear3 & "'"], 0)) = 0
...

myYear1, 2, and 3 hold value of the years and they are of type "string". I don't want to hardcode the years in there but to make it variable as possible depending on the user input.

The above code gave runtime error - "Microsoft office Access can't find the field '|' refered to in your expression"

BTW, if the years are hardcoded, it then works.

Thank you in advance

 

[Rich]

Whoever built this db should do some research on normalisation

 

[associates]

Thank you Rich for your reply.

Yes, but do you have the solution to my question?

Thank you in advance

 

[Rich]

The answer is to normalise this data with a series of append queries and then use a totals query, remember the adage a stich in time....