Conditions on the CanGrow property

[Slez via AccessMonster.com]

I have 3 controls in a section of a report. All 3 of them have the CanGrow
property set to "yes" so that if data exists, they expand to show that data
on the report. Data always exists in one of the controls because it is the
sum of a quantity field in the detail. Is it possible to set that one to
only grow if something exists in the other two?

For example:
Text1 contains a sum (as mentioned previously). If Text2 & Text3 do not
contain any data, Text1 stays shrunken. Conversely, if either Text2 or Text3
contain data and grow to display that data, Text1 grows as well.

Any suggestions are appreciated!

 

[Allen Browne]

Try a Control Source that evaluates to Null if the other 2 fields are null.

Like this:
=IIf([Text2] Is Null AND [Text3] Is Null, Null, Sum([F3]))

 

[Slez via AccessMonster.com]

That works perfectly! Thank you so much for the help!

Try a Control Source that evaluates to Null if the other 2 fields are null.

Like this:
=IIf([Text2] Is Null AND [Text3] Is Null, Null, Sum([F3]))

I have 3 controls in a section of a report. All 3 of them have the
CanGrow

[quoted text clipped - 10 lines]

Text3
contain data and grow to display that data, Text1 grows as well.

 

[Slez via AccessMonster.com]

One additional question Allen...
I ended up needing to concatenate 2 fields in the report's source query.
Basically, I combined the contents of another Text control with Text2 and
added a "space hypen space" (" - ") in between. I changed the Control Source
to:

=IIf([Text2] Is Null AND [Text2]=" - " AND [Text3] Is Null,Null,Sum([Quantity]
))

I've tried to change the first AND to OR, and I've tried some parentheses
within the expression, but it does not change the results. How can I make
Text1 shrink with the the " - " in the Text2 control?
Any additional help is greatly appreciated!

Try a Control Source that evaluates to Null if the other 2 fields are null.

Like this:
=IIf([Text2] Is Null AND [Text3] Is Null, Null, Sum([F3]))

I have 3 controls in a section of a report. All 3 of them have the
CanGrow

[quoted text clipped - 10 lines]

Text3
contain data and grow to display that data, Text1 grows as well.

 

[Allen Browne]

What is the condition when you need this to shrink? What's in Text2?

Perhaps you want:
=IIf([Text2]=" - " AND [Text3] Is Null, Null, Sum([Quantity]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

One additional question Allen...
I ended up needing to concatenate 2 fields in the report's source query.
Basically, I combined the contents of another Text control with Text2 and
added a "space hypen space" (" - ") in between. I changed the Control
Source
to:

=IIf([Text2] Is Null AND [Text2]=" - " AND [Text3] Is
Null,Null,Sum([Quantity]
))

I've tried to change the first AND to OR, and I've tried some parentheses
within the expression, but it does not change the results. How can I make
Text1 shrink with the the " - " in the Text2 control?
Any additional help is greatly appreciated!

Try a Control Source that evaluates to Null if the other 2 fields are
null.

Like this:
=IIf([Text2] Is Null AND [Text3] Is Null, Null, Sum([F3]))

I have 3 controls in a section of a report. All 3 of them have the
CanGrow

[quoted text clipped - 10 lines]

Text3
contain data and grow to display that data, Text1 grows as well.

 

[Slez via AccessMonster.com]

Thanks for the response. That works. One other thing that's now occurring
is the Text2 control always displays because the value " - " always exists.
If there is additional value from the Control Source: ComponentColor, I need
that to display, but if only " - ", I want it to shrink up, as if the value
was Null. Is there a way to make that shrink if it only contains " - " and
nothing else?

I tried the following in the control source, but got "#Error" on the report.
=IIf([Text2]=" - ",Null,[ComponentColor])

Thank you in advance for any further suggestions!

What is the condition when you need this to shrink? What's in Text2?

Perhaps you want:
=IIf([Text2]=" - " AND [Text3] Is Null, Null, Sum([Quantity]))

One additional question Allen...
I ended up needing to concatenate 2 fields in the report's source query.

[quoted text clipped - 23 lines]

 

[Allen Browne]

Same thing again for that control.

If it says:
=[A] & " - " &
change to:
=IIf([A] Is Null AND Is Null, Null, [A] & " - " & )

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the response. That works. One other thing that's now
occurring
is the Text2 control always displays because the value " - " always
exists.
If there is additional value from the Control Source: ComponentColor, I
need
that to display, but if only " - ", I want it to shrink up, as if the
value
was Null. Is there a way to make that shrink if it only contains " - "
and
nothing else?

I tried the following in the control source, but got "#Error" on the
report.
=IIf([Text2]=" - ",Null,[ComponentColor])

Thank you in advance for any further suggestions!

What is the condition when you need this to shrink? What's in Text2?

Perhaps you want:
=IIf([Text2]=" - " AND [Text3] Is Null, Null, Sum([Quantity]))

One additional question Allen...
I ended up needing to concatenate 2 fields in the report's source query.

[quoted text clipped - 23 lines]

Text3
contain data and grow to display that data, Text1 grows as well.

 

[Slez via AccessMonster.com]

Just so I'm understanding you properly... It seems as though you're
concatenating the fields in the report. I had done that in the query. Is
one way better than the other?
Thanks

Same thing again for that control.

If it says:
=[A] & " - " &
change to:
=IIf([A] Is Null AND Is Null, Null, [A] & " - " & )

Thanks for the response. That works. One other thing that's now
occurring

[quoted text clipped - 24 lines]

 

[Allen Browne]

You can do it either way.

Generally my preference is to do it in the query if I need a result to
operate on (e.g. a value to sum later), but in the report if I just need it
for a single occurrence.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Just so I'm understanding you properly... It seems as though you're
concatenating the fields in the report. I had done that in the query. Is
one way better than the other?
Thanks

Same thing again for that control.

If it says:
=[A] & " - " &
change to:
=IIf([A] Is Null AND Is Null, Null, [A] & " - " & )

Thanks for the response. That works. One other thing that's now
occurring

[quoted text clipped - 24 lines]

Text3
contain data and grow to display that data, Text1 grows as well.

 

[Slez via AccessMonster.com]

I have this working properly now. Thanks for all of your help and advice!

You can do it either way.

Generally my preference is to do it in the query if I need a result to
operate on (e.g. a value to sum later), but in the report if I just need it
for a single occurrence.

Just so I'm understanding you properly... It seems as though you're
concatenating the fields in the report. I had done that in the query. Is

[quoted text clipped - 13 lines]