omit records based on a calculated field in access reports

[rivif]

Hi everyone,

I have created a report with a sub report. (WOW) On the report is a calculated field that subtracts some numbers from the sub report. All this works really great except I do not want the records to be in my report if equal to or less than 0.

Please don't say I need to create a field in a table.
Everyone states that doing so is a crime against the programmers decree and is punishable by WINDOWS!!!

Best regards,
RIVIF

 

[dhookom]

You need to explain better since you can't "subtract some numbers from the sub report" since a subreport is a control and not a number. Maybe you could take the time to type a few record values that might display in your report and suggest which ones should not display and why.

Duane
MS Access MVP

 

[rivif]

Good morning,

I miss spoke myself when I said "some", I meant a single field from the sub report. I will try to explain better.

I have a field on the report named Quantity and field on the sub-report named QuantityOut. The calculated field is called QuantityRemaining. The equation is "=Nz([Quantity])-Nz([OutboundSubform].Form!SumQuantityOut)".

The report's layout is as follows:

ID# B51348

Location Shipping Number Quantity Remain
11B? 305 62
1A N/M 0
3A 24 118
3A? 99 0

The records that should not be in the report is the zeroes.

I tried to use a query to create this report, but the query would not surrender records where nothing has gone "out". For as an Example if ID# B51348 arrived Shipping Number 305 with 62 items and all 62 items remain then the query will not show this record.

I am sorry for not explaining better in my previous post.

Best regards,
RIVIF

 

[Stevehewitt]

Hey,

Sorry - I am still not 100% sure what you mean. Do you just want to remove any 0's when or if they appear in the report?

Can't you just use conditional formatting to make the text white if 0?



Steve Hewitt

 

[rivif]

Hello again,

This what I get in the report now;

ID# B51348

Location Shipping Number Quantity Remain
11B? 305 62
1A N/M 0
3A 24 118
3A? 99 0

This is what I want:

ID# B51348

Location Shipping Number Quantity Remain
11B? 305 62
3A 24 11

Best regards,
RIVIF

 

[dhookom]

I think you could just use code in the On Format event to either cancel the printing of the section of make it invisible if QuantityRemaining is 0.
Me.Section(0).Visible = Me.QuantityRemaining <>0

Duane
MS Access MVP

 

[rivif]

Hi Duane,

That worked on removing the records. THANK-YOU!

Now that created another problem. The Labels are in a different group. They do not &quot;go away&quot;. This is what I get now:

ID# B51348

Location Shipping Number Quantity Remain
11B? 305 62
3A 24 118

ID# B51349

Location Shipping Number Quantity Remain


I tried the same code in the group header. It did not work. I tried refering to the detail section where the calculated field is and I put a copy of the calculated field in the group header.

Best regards,
RIVIF

 

[dhookom]

It isn't real clear which of your displayed lines are on the main report or subreport. However, I recommend creating a totals query that is similar to your subreport's record source but it groups by the Link Master/Child field. You can add columns/fields with the totals you would need to determine whether or not a record/section should display on your main report. Add this totals query to the record source of your main report and join the same fields that were in your link master/child properties. You can then add the calculated totals columns for filtering and other purposes.

Duane
MS Access MVP

 

[rivif]

Good morning,

I will try your advice. And let you know in a week or so.

Thanks,
RIVIF