Making a control invisible depending on calculated value in Access'97
| Author |
Message |
|
Robert Solomo
#1 / 9
|
 Making a control invisible depending on calculated value in Access'97 Hi. I have a database with inventory items and transactions. I now have a report that summarizes in the footer the quantity on hand. How do I hide this line when the quantity on hand is above a ReorderLevel, and display it otherwise. In other words, I want a report to only display items that need to be ordered depending on a calculated quantity_on_hand and a field entry of ReorderLevel? Thanks,
Rob
|
| Sun, 22 Aug 2004 12:25:45 GMT |
|
 |
|
Steve Schape
#2 / 9
|
Making a control invisible depending on calculated value in Access'97 Rob, One way would be to use a calculated control in the Footer, e.g. an
unbound textbox with controlsource something like...
=IIf((YourQOH_Calc)>[ReorderLevel],Null,(YourQOH_Calc))
You could also set the control's CanShrink property to Yes, so if not
displayed, it won't take up 'empty' space on the report.
- Steve Schapel, Microsoft Access MVP
Quote: >Hi. I have a database with inventory items and transactions. I now >have a report that summarizes in the footer the quantity on hand. How >do I hide this line when the quantity on hand is above a ReorderLevel, >and display it otherwise. In other words, I want a report to only >display items that need to be ordered depending on a calculated >quantity_on_hand and a field entry of ReorderLevel? >Thanks,
>Rob
|
| Sun, 22 Aug 2004 17:27:17 GMT |
|
|
|
Robert Solomo
#3 / 9
|
Making a control invisible depending on calculated value in Access'97 The problem with that is the QOH_Calc is not null, so the line takes up space even if this calculation is not visible. This leaves the report with a lot of blank lines that only contain an invisible QOH_Calc control. How do I get it so that this control also can be shrunk to zero size so the completely blank line (with this invisible non-blank control) is considered null and won't take up space on the report. I don't know if that's clear. I'll try again if needed.
Quote: > Rob, > One way would be to use a calculated control in the Footer, e.g. an
> unbound textbox with controlsource something like...
> =IIf((YourQOH_Calc)>[ReorderLevel],Null,(YourQOH_Calc))
> You could also set the control's CanShrink property to Yes, so if not
> displayed, it won't take up 'empty' space on the report.
> - Steve Schapel, Microsoft Access MVP
> >Hi. I have a database with inventory items and transactions. I now
> >have a report that summarizes in the footer the quantity on hand. How
> >do I hide this line when the quantity on hand is above a ReorderLevel,
> >and display it otherwise. In other words, I want a report to only
> >display items that need to be ordered depending on a calculated
> >quantity_on_hand and a field entry of ReorderLevel?
> >Thanks,
> >Rob
|
| Mon, 23 Aug 2004 10:25:28 GMT |
|
|
|
Steve Schape
#4 / 9
|
Making a control invisible depending on calculated value in Access'97 Rob, No, it is me who has to try again, I think, as I was obviously not
clear the first time. The QOH_Calc control doesn't take up a blank
line, because you don't have a control for QOH_Calc. Instead, you
have a control with the type of expression that I gave in my previous
post, and set its (and the Section's) CanShrink property to yes. In
the example expression I gave, you replace the (YourQOH_Calc) with
whatever your calculation formula is.
If we're still having problems with this, please give us more
specifics about your data and what your calculation involves, as it
may be easier if we get off the abstract level :-)
- Steve Schapel, Microsoft Access MVP
Quote: >The problem with that is the QOH_Calc is not null, so the line takes up >space even if this calculation is not visible. This leaves the report >with a lot of blank lines that only contain an invisible QOH_Calc >control. How do I get it so that this control also can be shrunk to >zero size so the completely blank line (with this invisible non-blank >control) is considered null and won't take up space on the report. >I don't know if that's clear. I'll try again if needed.
>> Rob,
>> One way would be to use a calculated control in the Footer, e.g. an
>> unbound textbox with controlsource something like...
>> =IIf((YourQOH_Calc)>[ReorderLevel],Null,(YourQOH_Calc))
>> You could also set the control's CanShrink property to Yes, so if not
>> displayed, it won't take up 'empty' space on the report.
>> - Steve Schapel, Microsoft Access MVP
>> >Hi. I have a database with inventory items and transactions. I now
>> >have a report that summarizes in the footer the quantity on hand. How
>> >do I hide this line when the quantity on hand is above a ReorderLevel,
>> >and display it otherwise. In other words, I want a report to only
>> >display items that need to be ordered depending on a calculated
>> >quantity_on_hand and a field entry of ReorderLevel?
>> >Thanks,
>> >Rob
|
| Mon, 23 Aug 2004 12:03:57 GMT |
|
|
|
Robert Solomo
#5 / 9
|
Making a control invisible depending on calculated value in Access'97 I made this database using the inventory control wizard. THis is the SQL form of the query I'm using: SELECT Products.CategoryID, Products.ProductName, Products.ProductDescription, Products.CatalogNumber, Products.UnitPrice, [Inventory Transactions].TransactionDate, [Inventory Transactions].UnitsReceived, [Inventory Transactions].UnitsSold, [Inventory Transactions].UnitsShrinkage, Products.ReorderLevel FROM Products LEFT JOIN [Inventory Transactions] ON Products.ProductID = [Inventory Transactions].ProductID ORDER BY Products.CategoryID, Products.ProductName; If I have a separate unbound control with the controlsource of
=Sum(nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage])) called
QOHCalc, then the other controls behave as they should with the =IIf
([QOHCalc]<=[ReorderLevel],[ProductName],Null) referring to QOHCalc. If
I try to include the QOHCalc formula in its entirety without the
separate unbound control, I get #NAME? errors. I tried all the
combinations of sum() I could think of. It only works with a separate
QOHCalc control to which the other fields can refer.
I don't know what else to do.
Thanks for listening.
Quote: > No, it is me who has to try again, I think, as I was obviously not > clear the first time. The QOH_Calc control doesn't take up a blank > line, because you don't have a control for QOH_Calc. Instead, you > have a control with the type of expression that I gave in my previous > post, and set its (and the Section's) CanShrink property to yes. In > the example expression I gave, you replace the (YourQOH_Calc) with > whatever your calculation formula is. > If we're still having problems with this, please give us more
> specifics about your data and what your calculation involves, as it
> may be easier if we get off the abstract level :-)
> - Steve Schapel, Microsoft Access MVP
> >The problem with that is the QOH_Calc is not null, so the line takes up
> >space even if this calculation is not visible. This leaves the report
> >with a lot of blank lines that only contain an invisible QOH_Calc
> >control. How do I get it so that this control also can be shrunk to
> >zero size so the completely blank line (with this invisible non-blank
> >control) is considered null and won't take up space on the report.
> >I don't know if that's clear. I'll try again if needed.
> >> Rob,
> >> One way would be to use a calculated control in the Footer, e.g. an
> >> unbound textbox with controlsource something like...
> >> =IIf((YourQOH_Calc)>[ReorderLevel],Null,(YourQOH_Calc))
> >> You could also set the control's CanShrink property to Yes, so if not
> >> displayed, it won't take up 'empty' space on the report.
> >> - Steve Schapel, Microsoft Access MVP
> >> >Hi. I have a database with inventory items and transactions. I now
> >> >have a report that summarizes in the footer the quantity on hand. How
> >> >do I hide this line when the quantity on hand is above a ReorderLevel,
> >> >and display it otherwise. In other words, I want a report to only
> >> >display items that need to be ordered depending on a calculated
> >> >quantity_on_hand and a field entry of ReorderLevel?
|
| Mon, 23 Aug 2004 15:17:13 GMT |
|
|
|
Steve Schape
#6 / 9
|
Making a control invisible depending on calculated value in Access'97 Robert, Without trying it out for myself, to be honest I am surprised that it
doesn't work to include the full calculation expression in your
textbox. Anyway, using the method that is working, try this... Set
the Visible property of the QOHCalc textbox to No, and close it right
up in design view so that it has no height at all. It should still
work by way of being referred to in your other textbox's formula, and
at the same time, have no visual impact on the report layout.
- Steve Schapel, Microsoft Access MVP
Quote: >I made this database using the inventory control wizard. THis is the >SQL form of the query I'm using: >SELECT Products.CategoryID, Products.ProductName, >Products.ProductDescription, Products.CatalogNumber, Products.UnitPrice, >[Inventory Transactions].TransactionDate, [Inventory >Transactions].UnitsReceived, [Inventory Transactions].UnitsSold, >[Inventory Transactions].UnitsShrinkage, Products.ReorderLevel >FROM Products LEFT JOIN [Inventory Transactions] ON Products.ProductID = >[Inventory Transactions].ProductID >ORDER BY Products.CategoryID, Products.ProductName; >If I have a separate unbound control with the controlsource of
>=Sum(nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage])) called
>QOHCalc, then the other controls behave as they should with the =IIf
>([QOHCalc]<=[ReorderLevel],[ProductName],Null) referring to QOHCalc. If
>I try to include the QOHCalc formula in its entirety without the
>separate unbound control, I get #NAME? errors. I tried all the
>combinations of sum() I could think of. It only works with a separate
>QOHCalc control to which the other fields can refer.
>I don't know what else to do.
>Thanks for listening.
>> No, it is me who has to try again, I think, as I was obviously not
>> clear the first time. The QOH_Calc control doesn't take up a blank
>> line, because you don't have a control for QOH_Calc. Instead, you
>> have a control with the type of expression that I gave in my previous
>> post, and set its (and the Section's) CanShrink property to yes. In
>> the example expression I gave, you replace the (YourQOH_Calc) with
>> whatever your calculation formula is.
>> If we're still having problems with this, please give us more
>> specifics about your data and what your calculation involves, as it
>> may be easier if we get off the abstract level :-)
>> - Steve Schapel, Microsoft Access MVP
>> >The problem with that is the QOH_Calc is not null, so the line takes up
>> >space even if this calculation is not visible. This leaves the report
>> >with a lot of blank lines that only contain an invisible QOH_Calc
>> >control. How do I get it so that this control also can be shrunk to
>> >zero size so the completely blank line (with this invisible non-blank
>> >control) is considered null and won't take up space on the report.
>> >I don't know if that's clear. I'll try again if needed.
>> >> Rob,
>> >> One way would be to use a calculated control in the Footer, e.g. an
>> >> unbound textbox with controlsource something like...
>> >> =IIf((YourQOH_Calc)>[ReorderLevel],Null,(YourQOH_Calc))
>> >> You could also set the control's CanShrink property to Yes, so if not
>> >> displayed, it won't take up 'empty' space on the report.
>> >> - Steve Schapel, Microsoft Access MVP
>> >> >Hi. I have a database with inventory items and transactions. I now
>> >> >have a report that summarizes in the footer the quantity on hand. How
>> >> >do I hide this line when the quantity on hand is above a ReorderLevel,
>> >> >and display it otherwise. In other words, I want a report to only
>> >> >display items that need to be ordered depending on a calculated
>> >> >quantity_on_hand and a field entry of ReorderLevel?
|
| Mon, 23 Aug 2004 18:37:06 GMT |
|
|
|
Robert Solomo
#7 / 9
|
Making a control invisible depending on calculated value in Access'97 That helped, but there is still a lot of empty space on the report. It is now 5 pages with only 10 lines of inventory in the whole report. But it was 17 pages, so I'll have to live with this for a while. Thanks.
Quote: > Without trying it out for myself, to be honest I am surprised that it
> doesn't work to include the full calculation expression in your
> textbox. Anyway, using the method that is working, try this... Set
> the Visible property of the QOHCalc textbox to No, and close it right
> up in design view so that it has no height at all. It should still
> work by way of being referred to in your other textbox's formula, and
> at the same time, have no visual impact on the report layout.
> - Steve Schapel, Microsoft Access MVP
> >I made this database using the inventory control wizard. THis is the
> >SQL form of the query I'm using:
> >SELECT Products.CategoryID, Products.ProductName,
> >Products.ProductDescription, Products.CatalogNumber, Products.UnitPrice,
> >[Inventory Transactions].TransactionDate, [Inventory
> >Transactions].UnitsReceived, [Inventory Transactions].UnitsSold,
> >[Inventory Transactions].UnitsShrinkage, Products.ReorderLevel
> >FROM Products LEFT JOIN [Inventory Transactions] ON Products.ProductID =
> >[Inventory Transactions].ProductID
> >ORDER BY Products.CategoryID, Products.ProductName;
> >If I have a separate unbound control with the controlsource of
> >=Sum(nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage])) called
> >QOHCalc, then the other controls behave as they should with the =IIf
> >([QOHCalc]<=[ReorderLevel],[ProductName],Null) referring to QOHCalc. If
> >I try to include the QOHCalc formula in its entirety without the
> >separate unbound control, I get #NAME? errors. I tried all the
> >combinations of sum() I could think of. It only works with a separate
> >QOHCalc control to which the other fields can refer.
> >I don't know what else to do.
> >Thanks for listening.
> >> No, it is me who has to try again, I think, as I was obviously not
> >> clear the first time. The QOH_Calc control doesn't take up a blank
> >> line, because you don't have a control for QOH_Calc. Instead, you
> >> have a control with the type of expression that I gave in my previous
> >> post, and set its (and the Section's) CanShrink property to yes. In
> >> the example expression I gave, you replace the (YourQOH_Calc) with
> >> whatever your calculation formula is.
> >> If we're still having problems with this, please give us more
> >> specifics about your data and what your calculation involves, as it
|
| Tue, 24 Aug 2004 15:56:24 GMT |
|
|
|
Steve Schape
#8 / 9
|
Making a control invisible depending on calculated value in Access'97 Robert, Don't give up that easily! There must be an explanation for large
amounts of empty space.
Where you've got controls with CanShrink set to Yes, Is the CanShrink
property of the report section itself also set to Yes? Are there
other controls on the same horizontal level which are not CanShrink?
Do you have Lines or Boxes drawn on your report in places where the
empty spaces are? Do you have PageBreak controls, or is the
ForceNewPage property set incorrectly for any of the report sections?
Are there actually empty vertical spaces in the report design?
Otherwise, are you able to describe to us the layout of your Report,
and where these blank spaces are occurring?
- Steve Schapel, Microsoft Access MVP
Quote: >That helped, but there is still a lot of empty space on the report. It >is now 5 pages with only 10 lines of inventory in the whole report. But >it was 17 pages, so I'll have to live with this for a while. >Thanks.
>> Without trying it out for myself, to be honest I am surprised that it
>> doesn't work to include the full calculation expression in your
>> textbox. Anyway, using the method that is working, try this... Set
>> the Visible property of the QOHCalc textbox to No, and close it right
>> up in design view so that it has no height at all. It should still
>> work by way of being referred to in your other textbox's formula, and
>> at the same time, have no visual impact on the report layout.
>> - Steve Schapel, Microsoft Access MVP
>> >I made this database using the inventory control wizard. THis is the
>> >SQL form of the query I'm using:
>> >SELECT Products.CategoryID, Products.ProductName,
>> >Products.ProductDescription, Products.CatalogNumber, Products.UnitPrice,
>> >[Inventory Transactions].TransactionDate, [Inventory
>> >Transactions].UnitsReceived, [Inventory Transactions].UnitsSold,
>> >[Inventory Transactions].UnitsShrinkage, Products.ReorderLevel
>> >FROM Products LEFT JOIN [Inventory Transactions] ON Products.ProductID =
>> >[Inventory Transactions].ProductID
>> >ORDER BY Products.CategoryID, Products.ProductName;
>> >If I have a separate unbound control with the controlsource of
>> >=Sum(nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage])) called
>> >QOHCalc, then the other controls behave as they should with the =IIf
>> >([QOHCalc]<=[ReorderLevel],[ProductName],Null) referring to QOHCalc. If
>> >I try to include the QOHCalc formula in its entirety without the
>> >separate unbound control, I get #NAME? errors. I tried all the
>> >combinations of sum() I could think of. It only works with a separate
>> >QOHCalc control to which the other fields can refer.
>> >I don't know what else to do.
>> >Thanks for listening.
>> >> No, it is me who has to try again, I think, as I was obviously not
>> >> clear the first time. The QOH_Calc control doesn't take up a blank
>> >> line, because you don't have a control for QOH_Calc. Instead, you
>> >> have a control with the type of expression that I gave in my previous
>> >> post, and set its (and the Section's) CanShrink property to yes. In
>> >> the example expression I gave, you replace the (YourQOH_Calc) with
>> >> whatever your calculation formula is.
>> >> If we're still having problems with this, please give us more
>> >> specifics about your data and what your calculation involves, as it
|
| Wed, 25 Aug 2004 02:49:23 GMT |
|
|
|
Robert Solomo
#9 / 9
|
Making a control invisible depending on calculated value in Access'97 All controls have canshrink of yes, no lines or boxes drawn anywhere, no pagebreak controls, forcenewpage prop is none. No vertical spaces. Is it possible for me to calculate the QOHCalc in a macro or proc triggered by "on format", so that the other needed controls can reference it? If so, how should I try this? The report is very simple; a details section that is not visible, and
one group footer with only 2 visible controls, the producename and
QOHCalc. The third invisible control holds QOHCalc; I was getting
#name? errors without the text control to hold the temporary QOHCalc
value.
I just don't know what else to do.
Does it make a difference that I'm working with Access 97?
Quote: > Don't give up that easily! There must be an explanation for large > amounts of empty space. > Where you've got controls with CanShrink set to Yes, Is the CanShrink
> property of the report section itself also set to Yes? Are there
> other controls on the same horizontal level which are not CanShrink?
> Do you have Lines or Boxes drawn on your report in places where the
> empty spaces are? Do you have PageBreak controls, or is the
> ForceNewPage property set incorrectly for any of the report sections?
> Are there actually empty vertical spaces in the report design?
> Otherwise, are you able to describe to us the layout of your Report,
> and where these blank spaces are occurring?
> - Steve Schapel, Microsoft Access MVP
> >That helped, but there is still a lot of empty space on the report. It
> >is now 5 pages with only 10 lines of inventory in the whole report. But
> >it was 17 pages, so I'll have to live with this for a while.
> >Thanks.
> >> Without trying it out for myself, to be honest I am surprised that it
> >> doesn't work to include the full calculation expression in your
> >> textbox. Anyway, using the method that is working, try this... Set
> >> the Visible property of the QOHCalc textbox to No, and close it right
> >> up in design view so that it has no height at all. It should still
> >> work by way of being referred to in your other textbox's formula, and
> >> at the same time, have no visual impact on the report layout.
> >> - Steve Schapel, Microsoft Access MVP
> >> >I made this database using the inventory control wizard. THis is the
> >> >SQL form of the query I'm using:
> >> >SELECT Products.CategoryID, Products.ProductName,
> >> >Products.ProductDescription, Products.CatalogNumber, Products.UnitPrice,
> >> >[Inventory Transactions].TransactionDate, [Inventory
> >> >Transactions].UnitsReceived, [Inventory Transactions].UnitsSold,
> >> >[Inventory Transactions].UnitsShrinkage, Products.ReorderLevel
> >> >FROM Products LEFT JOIN [Inventory Transactions] ON Products.ProductID =
> >> >[Inventory Transactions].ProductID
|
| Wed, 25 Aug 2004 09:31:19 GMT |
|
| |
|
