Need help supressing detail section if calc is 0 1

[Trogg]

I am producing a departmental report that looks like the below example. If the Total Dept calculation is 0 for both amounts I would like to skip or supress the detail for that department to reduce the size of the report. If the calculation produces a total for either or both amounts the detail needs to show. How can I accomplish this and if it requires code or VB I'll need help with doing that. Thanks in advance!

Dept Account Category AmntIn AmntOut

**.3100 Ik 110 $0.00
**.3100 Ik 206 $0.00
Total Dept $0.00 $0.00

**.3101 IM 801 $300.00
**.3101 OPBSLD 510 $0.00
**.3101 OPBSLD 761 $0.00
Total Dept $300.00 $0.00

**.3130 ID 110 $0.00
**.3130 ID 113 $0.00 $25.00
**.3130 OPS 762 $10.00
Total Dept $0.00 $35.00

 

[dhookom]

I would display the Group Header and add text boxes to display the totals.

Name: txtDeptInTot
COntrol Source: =Sum(....)

Name: txtDeptOutTot
Control SOurce: =Sum(...)

Then, in the On Format event of the detail section, add code like:

    If Me.txtDeptInTot = 0 And Me.txtDeptOutTot = 0 Then
        Cancel = True
     Else
        Cancel = False
     End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Trogg]

Thanks for the help... When I do the above I get a message that says it can't find the macro "If Me." The macro (or it's macro group)doesn't exist, or the macro is new and hasn't been saved. What have I done wrong? This is Access 2000 by the way, if it makes any difference.

 

[dhookom]

You need to add the code in the code/module window, not the "property" window. Go into the VBA window and paste the code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Trogg]

Thanks again and please forgive my lack of VB knowledge. I think that's in the right place now... no error message this time but I find I have possible nulls in the data as well. When I run the report now if one of the totals has nulls and one zeros it still prints the detail section. Can we take care of the nulls in the code as well? Sorry to be a burden..

 

[dhookom]

Change the code to

    If Nz(Me.txtDeptInTot,0) = 0 And Nz(Me.txtDeptOutTot,0) = 0 Then
        Cancel = True
     Else
        Cancel = False
     End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Trogg]

Thank you! I actually knew about the Nz function but didn't know to use it there, thanks. I think this is working however the header and footer for the skipped detail sections still show... how can I get them to not print. If thoe values are null or zero I want nothing to show. Do I insert the same code for those?

 

[dhookom]

You should be able to add similar code to the On Format event of the other sections.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Trogg]

Fantastic! Works exactly as I needed and I also learned a thing or two. Stars to you sir and thanks tremendously!