ACCESS REPORT DEFINITION - Supress a Group on a Condition?

[robertfuschetto via AccessMonster.com]

I am not the most proficient Access Report Writer but here goes....

I have a report that is grouped and grouped again therein. Sometimes there
is no data at ONE of the group levels. Access leaves some white space. Can
I have it supress? Also, can the report definition suppress a group based on
a condition...Ex: IF tablename.group = "ABDC" then suppress the group from
being reported.

Thanks!

Ex:
Table Structure:

Country
State
Amt

Here is the data:

Country State Amt
USA PA 500
USA NJ 1000
CHINA none 3000
RUSSIA none 450

The report should look like this:

USA
PA 500
NJ 1000
total 1500

CHINA 3000

RUSSIA 450

**Under View Sorting and Grouping I'd have Country and State with totals by
country



I want to group the report by Country then State then County. I want totals
by country but if state data does not exist then I want the total suppressed
rather than repeated. If state or county is none, I do not want 'none'
printed. This is not hte best example - I hope it gets the point across.

 

[Marshall Barton]

I am not the most proficient Access Report Writer but here goes....

I have a report that is grouped and grouped again therein. Sometimes there
is no data at ONE of the group levels. Access leaves some white space. Can
I have it supress? Also, can the report definition suppress a group based on
a condition...Ex: IF tablename.group = "ABDC" then suppress the group from
being reported.

Thanks!

Ex:
Table Structure:

Country
State
Amt

Here is the data:

Country State Amt
USA PA 500
USA NJ 1000
CHINA none 3000
RUSSIA none 450

The report should look like this:

USA
PA 500
NJ 1000
total 1500

CHINA 3000

RUSSIA 450

**Under View Sorting and Grouping I'd have Country and State with totals by
country

I want to group the report by Country then State then County. I want totals
by country but if state data does not exist then I want the total suppressed
rather than repeated. If state or county is none, I do not want 'none'
printed. This is not hte best example - I hope it gets the point across.

You can supress the State group header/footer sections by
using this line of code in their Format event procedure:

Cancel = (Me.State = "none")

 

[robertfuschetto via AccessMonster.com]

sorry, do I right click 'Properties' on the footer/header, look at the 'On
Format' area and paste this in?

I am not the most proficient Access Report Writer but here goes....

[quoted text clipped - 39 lines]

rather than repeated. If state or county is none, I do not want 'none'
printed. This is not hte best example - I hope it gets the point across.

You can supress the State group header/footer sections by
using this line of code in their Format event procedure:

Cancel = (Me.State = "none")

 

[robertfuschetto via AccessMonster.com]

when I paste it in I get this:

The expression On Format you entered as the event property setting produced
the follwoing error:
The object doesn't contain the automation oblect 'Cancel'.

sorry, do I right click 'Properties' on the footer/header, look at the 'On
Format' area and paste this in?
[quoted text clipped - 6 lines]

Cancel = (Me.State = "none")

 

[Marshall Barton]

No , you don't put that in the OnFormat property. It goes
in the Format **event procedure**.

Select [Event Procedure] in the OnFormat property, then
click on the builder button [...] over in the right margin
to get to the VBA procedure for the event. Enter the line
of code between the Sub . . . and End Sub lines.
--
Marsh
MVP [MS Access]

when I paste it in I get this:

The expression On Format you entered as the event property setting produced
the follwoing error:
The object doesn't contain the automation oblect 'Cancel'.

sorry, do I right click 'Properties' on the footer/header, look at the 'On
Format' area and paste this in?

I am not the most proficient Access Report Writer but here goes....

[quoted text clipped - 6 lines]

Cancel = (Me.State = "none")

 

[robertfuschetto via AccessMonster.com]

THANKS...I tried that but still got an error...thought maybe the command was
CancelEvent.....anyway I'll try again. You have my appreciation sir!

No , you don't put that in the OnFormat property. It goes
in the Format **event procedure**.

Select [Event Procedure] in the OnFormat property, then
click on the builder button [...] over in the right margin
to get to the VBA procedure for the event. Enter the line
of code between the Sub . . . and End Sub lines.

when I paste it in I get this:

[quoted text clipped - 10 lines]

 

[robertfuschetto via AccessMonster.com]

I have searched many posts on this CANCEL command and I think I'm in a pickle.
What is the 'Me' in Me.state = "none". I think my version of access might be
choking on it. We are on Access 2003.

THANKS...I tried that but still got an error...thought maybe the command was
CancelEvent.....anyway I'll try again. You have my appreciation sir!

No , you don't put that in the OnFormat property. It goes
in the Format **event procedure**.

[quoted text clipped - 8 lines]

 

[Marshall Barton]

Cancel is an argument to the Format event procedure.
Setting it to True tells Access that you do not want the
section to be printed/displayed.

Me is the class object that the code is running in. In this
case it's the report object. Using Me.State indicates that
you want the value of the State text box in the report.

When you are having trouble figuring out why you are getting
an error, it would be a big help if you told us exactly what
error message you are seeing.
--
Marsh
MVP [MS Access]

I have searched many posts on this CANCEL command and I think I'm in a pickle.
What is the 'Me' in Me.state = "none". I think my version of access might be
choking on it. We are on Access 2003.

THANKS...I tried that but still got an error...thought maybe the command was
CancelEvent.....anyway I'll try again. You have my appreciation sir!

No , you don't put that in the OnFormat property. It goes
in the Format **event procedure**.

[quoted text clipped - 8 lines]

Cancel = (Me.State = "none")

 

[robertfuschetto via AccessMonster.com]

OK, when I put this code in:

Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.EncounterType = "none")
End Sub

I get this error:
Microsoft Access can't find the field EncounterType reffered to in your
expresseion. Error #2465

(the field name IS spelled correctly!)


When I put this in:
Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (tblPhysicianProfileReportData.EncounterType = "none")
End Sub


I get this error:
Object Required, Error 424


I have tried this too:
Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)

If tblPhysicianProfileReportData.EncounterType = "none" Then
Cancel = True
End If
End Sub


I get the Object Required error again.


SOMETHING IS AWRY HERE!!!!

Cancel is an argument to the Format event procedure.
Setting it to True tells Access that you do not want the
section to be printed/displayed.

Me is the class object that the code is running in. In this
case it's the report object. Using Me.State indicates that
you want the value of the State text box in the report.

When you are having trouble figuring out why you are getting
an error, it would be a big help if you told us exactly what
error message you are seeing.

I have searched many posts on this CANCEL command and I think I'm in a pickle.
What is the 'Me' in Me.state = "none". I think my version of access might be

[quoted text clipped - 8 lines]

 

[robertfuschetto via AccessMonster.com]

Ahhhh...amy I suppose to refrerence the text box name or control source. I
am now referencing the text box name and the error goes away...though the
suspression is not being cancelled.

OK, when I put this code in:

Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.EncounterType = "none")
End Sub

I get this error:
Microsoft Access can't find the field EncounterType reffered to in your
expresseion. Error #2465

(the field name IS spelled correctly!)

When I put this in:
Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (tblPhysicianProfileReportData.EncounterType = "none")
End Sub

I get this error:
Object Required, Error 424

I have tried this too:
Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)

If tblPhysicianProfileReportData.EncounterType = "none" Then
Cancel = True
End If
End Sub

I get the Object Required error again.

SOMETHING IS AWRY HERE!!!!

Cancel is an argument to the Format event procedure.
Setting it to True tells Access that you do not want the

[quoted text clipped - 12 lines]

 

[robertfuschetto via AccessMonster.com]

GOT IT!!!!!!!!!!!!!!!!!!!!!!!!

THANK YOU!!!!!!!!!!!!!!!!!!

Ahhhh...amy I suppose to refrerence the text box name or control source. I
am now referencing the text box name and the error goes away...though the
suspression is not being cancelled.

OK, when I put this code in:

[quoted text clipped - 33 lines]

 

[Marshall Barton]

That error indicates that there is no text box bound to the
EncounterType field in the report's record source
table/query.
--
Marsh
MVP [MS Access]

OK, when I put this code in:

Private Sub GroupFooter5_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.EncounterType = "none")
End Sub

I get this error:
Microsoft Access can't find the field EncounterType reffered to in your
expresseion. Error #2465

(the field name IS spelled correctly!) [snip]

Cancel is an argument to the Format event procedure.
Setting it to True tells Access that you do not want the
section to be printed/displayed.

Me is the class object that the code is running in. In this
case it's the report object. Using Me.State indicates that
you want the value of the State text box in the report.

When you are having trouble figuring out why you are getting
an error, it would be a big help if you told us exactly what
error message you are seeing.

 

[Marshall Barton]

Way to go.

Once again, perseverance and intellect triumph over
recalcitrant code ;-)
--
Marsh
MVP [MS Access]

GOT IT!!!!!!!!!!!!!!!!!!!!!!!!

THANK YOU!!!!!!!!!!!!!!!!!!

Ahhhh...amy I suppose to refrerence the text box name or control source. I
am now referencing the text box name and the error goes away...though the
suspression is not being cancelled.

OK, when I put this code in:

[quoted text clipped - 33 lines]

Cancel = (Me.State = "none")