filtering/querying records based on complex calculations

[proben930]

I have a fairly major problem: I am trying to create a report that does some fairly complex calculations on a large quantity of records. The calculations must be done in VB due to their complexity. At the end of the calculations, I would like the report to supress the ones that come up at zero. How can this be done? The calculations cannot be done in a query, and i cannot get a filter to work. Any ideas?

 

[Rich]

Hide a field or the whole record?

 

[proben930]

whole record

 

[KKilfoil]

1)Put some code in the 'OnFormat' event of your report detail section that examines the field(s) where you are looking for the zero value, and if a zero is found, set the visible property of EACH control to 'False'. To avoid leaving an unsightly space, you have to set the height of each control and the detail section itself to a very small value.

-or-

2) Create one or more queries and redesign your vb calcualtions to be public user-defined functions that can be called from within the query. Then all you have to do is filter out the zero ones for this particular problem.

The advantage of approach 2 is that you can use these functions anywhere (reports, queries, forms, modules) and they can be 'reused' again without any more work.

HTH