Hide Entire Records With Zero Values

[Tor_Fey]

Good Morning All;

I am looking for help with an access 2003 report; what I need to do is to not show an entire record on my report; if the total of my total field is zero. Is there any VBA code I can use to accomplish this?

Your help as always is very much appreciated

Kind Regards
Tor Fey

 

[NauticalGent]

Good morning Tor_Fey,

Rather than hide the records at report run-time, why not base the report on a query that excludes those records?

 

[Tor_Fey]

Hi NauticalGent;

Unfortunately; the calculation field is not in my query; it is an unbound fields that counts days taken between two date fields; this is why I need some kind of 'On Open' event that will hide all the records with a value of Zero in my unbound calculation field.

Regards
Tor Fey

Good morning Tor_Fey,

Rather than hide the records at report run-time, why not base the report on a query that excludes those records?

 

[NauticalGent]

Understood. My suggestion would be to alter the query that report is based on.

 

[NauticalGent]

For example, lets say the two fields are [qty] and [price]. Make a calculated field in your query like [TotalPrice]. The syntax would be TotalPrice:[qty]*[Price].

In the criteria field, enter > 0. This should ensure your report is based only of records where the total price is greater then 0 (zero).

Makes sense?

 

[Tor_Fey]

This doesn't really work as I have 3 calculated fields that require to be added up and only show records >0, if you have a look at the attached query, how does one go about summing fields in a query? as when I try it nothing is displayed?

So I need to sum up the expressions of my query: totaldays1, totaldays2 and totaldays3 in my query; how does one do this?

Kind Regards
Tor Fey

For example, lets say the two fields are [qty] and [price]. Make a calculated field in your query like [TotalPrice]. The syntax would be TotalPrice:[qty]*[Price].

In the criteria field, enter > 0. This should ensure your report is based only of records where the total price is greater then 0 (zero).

Makes sense?

 

[arnelgp]

just add the Criteria below each of your calculated fields in the query:

> 0

 

[Tor_Fey]

arnelgp;

This isn't going to work; I need to create an expression in my query; that adds together 3 other expressions to give a total and only to display records >0 for that 'totalalldays' column, so for example:

'totalalldays: [totaldays1] + [totaldays2] + [totaldays3]'

please not; in my query; totaldays1, totaldays2 and totaldays3 are already expressions in my query. and if I add this expression: 'totalalldays: [totaldays1] + [totaldays2] + [totaldays3]' with the >0 my query doesn't run? :banghead:

Regards
Tor Fey

just add the Criteria below each of your calculated fields in the query:

> 0

 

[arnelgp]

you dont need to add another calculated field in your query. if the three calculated field all yields zero (0), then all conditions are met. that is:

[totaldays1] + [totaldays2] + [totaldays3]
0 + 0 + 0 = 0

so just add criteria to:
[totaldays1]
Criteria: > 0

[totaldays2]
Criteria: > 0

[totaldays3]
Criteria: > 0

 

[plog]

Ugh, that's a super ugly query. When you start numerating field names (totaldays1, totaldays2) you are probably doing it wrong. Data should grow vertically (with more records) not horizontally (with more columns). I think you might have a table structure issue if that query is based on a table with fields like check_start_date1, check_start_date2.

This probably becomes very trivial to accomplish if you structure your data correctly. If structured correctly you can probably use SQL aggregate functions (SUM, COUNT, etc.) to limit your data to just those you want.

Do you have numerated field names in your table? check_start_date2, check_start_date3, etc.?

 

[Tor_Fey]

Hi arnelgp;

the fields 'totaldays1', 'totaldays2' and 'totaldays3' already add together date fields individually; I then need to add these fields together to get a running total. if the total is then '0' I don't want the records to show on the report.

Regards
Tor Fey

you dont need to add another calculated field in your query. if the three calculated field all yields zero (0), then all conditions are met. that is:

[totaldays1] + [totaldays2] + [totaldays3]
0 + 0 + 0 = 0

so just add criteria to:
[totaldays1]
Criteria: > 0

[totaldays2]
Criteria: > 0

[totaldays3]
Criteria: > 0

 

[Tor_Fey]

Hi Plog;

The data shown; is only a quick dirty test database to try and demonstrate what I need to do; I wasn't aloud to post any data from the actual database this will be run in.

Regards
Tor Fey

Ugh, that's a super ugly query. When you start numerating field names (totaldays1, totaldays2) you are probably doing it wrong. Data should grow vertically (with more records) not horizontally (with more columns). I think you might have a table structure issue if that query is based on a table with fields like check_start_date1, check_start_date2.

This probably becomes very trivial to accomplish if you structure your data correctly. If structured correctly you can probably use SQL aggregate functions (SUM, COUNT, etc.) to limit your data to just those you want.

Do you have numerated field names in your table? check_start_date2, check_start_date3, etc.?

 

[Tor_Fey]

All;

Attached is the what I am trying to achieve in my query as suggested; unfortunately I cannot get the query to run correctly; any help would be appreciated.

Regards
Tor Fey

 

[NauticalGent]

Tor_Fey,

At the risk of shooting from the hip, I am certain that your issue is trying to include calculated fields for further calculations in the SAME query. I tired to do something that this before and it seemed to confuse Access.

Bad table and query design aside (you should really heed the advice you have been given), you may try to break up your one query into two; use the first query to determine the values and then use a second query to use what get the records you want.

 

[Tor_Fey]

Hi NauticalGent;

I have just discovered this; and now I can confirm everything is working correctly.

Thank you so much for all your help .

Kind Regards
Tor Fey

Tor_Fey,

At the risk of shooting from the hip, I am certain that your issue is trying to include calculated fields for further calculations in the SAME query. I tired to do something that this before and it seemed to confuse Access.

Bad table and query design aside (you should really heed the advice you have been given), you may try to break up your one query into two; use the first query to determine the values and then use a second query to use what get the records you want.

 

[NauticalGent]

Great to hear, glad I could help out!

Cheers