Null text box fields when group heading is null

  • Thread starter Thread starter Richard Harison
  • Start date Start date
R

Richard Harison

I apologize if this has been covered before but..
I have a report grouped by "secondary category" (There is ALWAYS a primary
category.) The database contains a number of records which have NO secondary
category. I have already modified the appropriate grouping header text box
as such:

=IIf([occurrencetype2] Is Not Null,[occurrencetype2]). So no grouping
header appears--works great

The problem is that each header, null or not, has a number of text box
fields connected with it to be filled in. Is there any way to marry the
command to ignore a group header which is null to an action that will also
null any & all text boxes associated with that nulled group header?
I have tried IIF statements on the data text boxes, and it works then the
group header is null, but creates error messages on the info where the group
header is not null.
MUCH THANKS
 
Richard

Not sure I completely understand your situation, but I'm wondering if you
have tried the CanGrow/CanShrink properties for the individual controls
and/or the section?

Good luck

Jeff Boyce
<Access MVP>
 
Thank you for your help so far
Yes I have used CanShrink property in other situations. In this case, the
individual controls in the detail section as well as the section (a group
header) have CanShrink set to yes

I'll pretend the detail section has only one control in it, for the sake of
simplicity

My group Header is based on the field named [type2] (some of the
records accessed by the report contain null values in this field )
Both the grouping level and the header control have this statement in the
control source property:

=IIf([type2] Is Not Null,[type2]) 'print type2 as group header if not
null -- works fine

The problems lies in the control (text box) in the detail section which I
want to shrink when [type2] group header is null
The control name is [IncidentDate], the same name for the field as in the
query the report is bound to
The control source is this line:

=IIf([type2] Is Null,Null,[IncidentDate])

This works fine when the group header is indeed null, but when it isn't I
get #error.

All I am asking is that when the group header [type2] is null, is there a
simple way to collapse all the controls in the detail box with one stroke,
including a subtotal in the group footer?.
THANK YOU...
All the Best . . .
Richard Harison
 
Richard

Still not sure I'm visualizing your design...

If you can "hide" the (group header) section, it sounds like you are
concerned with a subtotal control in another section. Have you hidden that
section as well? Have you looked into setting the .Visible property instead
of the CanGrow/CanShrink?

Good luck

Jeff Boyce
<Access MVP>

Richard Harison said:
Thank you for your help so far
Yes I have used CanShrink property in other situations. In this case, the
individual controls in the detail section as well as the section (a group
header) have CanShrink set to yes

I'll pretend the detail section has only one control in it, for the sake of
simplicity

My group Header is based on the field named [type2] (some of the
records accessed by the report contain null values in this field )
Both the grouping level and the header control have this statement in the
control source property:

=IIf([type2] Is Not Null,[type2]) 'print type2 as group header if not
null -- works fine

The problems lies in the control (text box) in the detail section which I
want to shrink when [type2] group header is null
The control name is [IncidentDate], the same name for the field as in the
query the report is bound to
The control source is this line:

=IIf([type2] Is Null,Null,[IncidentDate])

This works fine when the group header is indeed null, but when it isn't I
get #error.

All I am asking is that when the group header [type2] is null, is there a
simple way to collapse all the controls in the detail box with one stroke,
including a subtotal in the group footer?.
THANK YOU...
All the Best . . .
Richard Harison


Jeff Boyce said:
Richard

Not sure I completely understand your situation, but I'm wondering if you
have tried the CanGrow/CanShrink properties for the individual controls
and/or the section?

Good luck

Jeff Boyce
Click to expand...
Click to expand...
 
Sorry, Jeff...perhaps I am not that good at articulating things. I'll try
it graphically:

----------------------------------------------------------------------------
=IIf([typ2] Is Not Null,[typ2]) Header
----------------------------------------------------------------------------
_____________________________________
| control source : =IIf([typ2] Is Not Null,[typ2]) |
| control name : txtTyp2 |
|____________________________________|
----------------------------------------------------------------------------
Detail
----------------------------------------------------------------------------
_____________________________________
| control source: =IIf([typ2] Is Not Null,[aDate] |
| control name : txtaDate |
|_____________________________________|

----------------------------------------------------------------------------
=IIf([typ2] Is Not Null,[typ2]) Footer
----------------------------------------------------------------------------
_________________________________________
|control source:=IIf([typ2] Is Not Null,Count([aDate])) |
|control name: txtSubTot |
|_________________________________________|


[Typ2] is a text field in the underlying Query
[aDate] is a date field in the underlying Query
txtaDate CanShrink set to yes
txtSubTot CanShrink set to yes
Group Header CanShrink set to yes
Group Footer CanShrink set to yes

I am wondering if there is a simple way to automatically suppress the entire
section (all controls within it) in one blow when the grouping variable
[type2] is Null
Also...I am not sure how to access the .visible property, since reports
don't seem to support Visual Basic events.
 
Richard

OnFormat is an event available to the report -- could you set the .Visible
property there?

Good luck

Jeff Boyce
<Access MVP>

Richard Harison said:
Sorry, Jeff...perhaps I am not that good at articulating things. I'll try
it graphically:

-------------------------------------------------------------------------- --
=IIf([typ2] Is Not Null,[typ2]) Header
-------------------------------------------------------------------------- --
_____________________________________
| control source : =IIf([typ2] Is Not Null,[typ2]) |
| control name : txtTyp2 |
|____________________________________|
-------------------------------------------------------------------------- --
Detail
--------------------------------------------------------------------------
Click to expand...
--
_____________________________________
| control source: =IIf([typ2] Is Not Null,[aDate] |
| control name : txtaDate |
|_____________________________________|

-------------------------------------------------------------------------- --
=IIf([typ2] Is Not Null,[typ2]) Footer
-------------------------------------------------------------------------- --
_________________________________________
|control source:=IIf([typ2] Is Not Null,Count([aDate])) |
|control name: txtSubTot |
|_________________________________________|


[Typ2] is a text field in the underlying Query
[aDate] is a date field in the underlying Query
txtaDate CanShrink set to yes
txtSubTot CanShrink set to yes
Group Header CanShrink set to yes
Group Footer CanShrink set to yes

I am wondering if there is a simple way to automatically suppress the entire
section (all controls within it) in one blow when the grouping variable
[type2] is Null
Also...I am not sure how to access the .visible property, since reports
don't seem to support Visual Basic events.

--
All the Best . . .
Richard Harison
Jeff Boyce said:
Richard

Still not sure I'm visualizing your design...

If you can "hide" the (group header) section, it sounds like you are
concerned with a subtotal control in another section. Have you hidden that
section as well? Have you looked into setting the .Visible property instead
of the CanGrow/CanShrink?

Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Jeff, Thanks for your expertise & patience. I do have it working in the way I
outlined below, but I was hoping there was a blanket command that could be
invoked at the section level to collapse all controls in the section when the
group header control was Null. I did check the section property and YES there
are ways to access Visual Basic in a report!! News to me!! I'll try it
tomorrow. I assume the code would be a simple IF/THEN as below:

IF [typ2] is Null THEN
control1.visible =false
control2.visible=false
END IF

or something like that!!
THANKS AGAIN!!
--
All the Best . . .
Richard Harison
Jeff Boyce said:
Richard

OnFormat is an event available to the report -- could you set the .Visible
property there?

Good luck

Jeff Boyce
<Access MVP>

Richard Harison said:
Sorry, Jeff...perhaps I am not that good at articulating things. I'll try
it graphically:

-------------------------------------------------------------------------- --
=IIf([typ2] Is Not Null,[typ2]) Header
-------------------------------------------------------------------------- --
_____________________________________
| control source : =IIf([typ2] Is Not Null,[typ2]) |
| control name : txtTyp2 |
|____________________________________|
-------------------------------------------------------------------------- --
Detail
--------------------------------------------------------------------------
Click to expand...
--
_____________________________________
| control source: =IIf([typ2] Is Not Null,[aDate] |
| control name : txtaDate |
|_____________________________________|

-------------------------------------------------------------------------- --
=IIf([typ2] Is Not Null,[typ2]) Footer
-------------------------------------------------------------------------- --
_________________________________________
|control source:=IIf([typ2] Is Not Null,Count([aDate])) |
|control name: txtSubTot |
|_________________________________________|


[Typ2] is a text field in the underlying Query
[aDate] is a date field in the underlying Query
txtaDate CanShrink set to yes
txtSubTot CanShrink set to yes
Group Header CanShrink set to yes
Group Footer CanShrink set to yes

I am wondering if there is a simple way to automatically suppress the entire
section (all controls within it) in one blow when the grouping variable
[type2] is Null
Also...I am not sure how to access the .visible property, since reports
don't seem to support Visual Basic events.

--
All the Best . . .
Richard Harison
Jeff Boyce said:
Richard

Still not sure I'm visualizing your design...

If you can "hide" the (group header) section, it sounds like you are
concerned with a subtotal control in another section. Have you hidden that
section as well? Have you looked into setting the .Visible property instead
of the CanGrow/CanShrink?

Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
Click to expand...
 
Jeff, Thanks for your expertise & patience. I do have it working in the way I
outlined below, but I was hoping there was a blanket command that could be
invoked at the section level to collapse all controls in the section when the
group header control was Null. I did check the section property and YES there
are ways to access Visual Basic in a report!! News to me!! I'll try it
tomorrow. I assume the code would be a simple IF/THEN as below:

IF [typ2] is Null THEN
control1.visible =false
control2.visible=false
END IF

or something like that!!
THANKS AGAIN!!
 
Jeff, Thanks for your expertise & patience. I do have it working in the way I
outlined below, but I was hoping there was a blanket command that could be
invoked at the section level to collapse all controls in the section when the
group header control was Null. I did check the section property and YES there
are ways to access Visual Basic in a report!! News to me!! I'll try it
tomorrow. I assume the code would be a simple IF/THEN as below:

IF [typ2] is Null THEN
control1.visible =false
control2.visible=false
END IF

or something like that!!
THANKS AGAIN!!
 
Back
Top