Ugly Error message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I get rid of the above Ugly "Error" Message if control reference is
empty? Below is a copy of a calculation at the report level which generates
an ugly "error", if the following/next to follow controls have no data.

=[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales]-[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales]

Can this be resolved easily? if it can, how? Please correct the above
calculation as an example.

Thanks,
 
Use the Nz function to convert Null values to 0:

=Nz([rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales],0)
-Nz([rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales],0)--Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)"Jay" <[email protected]> wrote in messagenews:[email protected]...> How can I get rid of the above Ugly "Error" Message if control referenceis> empty? Below is a copy of a calculation at the report level whichgenerates> an ugly "error", if the following/next to follow controls have no data.>>=[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales]-[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales]>> Can this be resolved easily? if it can, how? Please correct the above> calculation as an example.>> Thanks,
 
Jay said:
How can I get rid of the above Ugly "Error" Message if control reference is
empty? Below is a copy of a calculation at the report level which generates
an ugly "error", if the following/next to follow controls have no data.

=[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales]-[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales]

Can this be resolved easily? if it can, how? Please correct the above
calculation as an example.
Click to expand...


Well those names make the expression really ugly, but you
asked for it ;-)

=IIf([rpt_Product_BrandyInfoData_Collection_PrevDay].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales],
0) -
IIf([rpt_Product_BrandyInfoData_Collection_Daily].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales],
0)
 
Thanks, Marshall it worked pretty good. Now I need to sum the value on the
report footer and I keep getting the dreaded #Error" message there now too!
Any ideas for resolving that?

Thanks,




Marshall Barton said:
Jay said:
How can I get rid of the above Ugly "Error" Message if control reference is
empty? Below is a copy of a calculation at the report level which generates
an ugly "error", if the following/next to follow controls have no data.

=[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales]-[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales]

Can this be resolved easily? if it can, how? Please correct the above
calculation as an example.
Click to expand...


Well those names make the expression really ugly, but you
asked for it ;-)

=IIf([rpt_Product_BrandyInfoData_Collection_PrevDay].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales],
0) -
IIf([rpt_Product_BrandyInfoData_Collection_Daily].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales],
0)
Click to expand...
 
Thanks, Marshall

It kind of worked, only, now I have 0.00 all the way down the column where
there is no data. What I'd like is to show is nothing, if there is no data to
calculate against. Is that possible?

Then, I'd like to sum the total of that column in the report footer. Is that
possible?



Marshall Barton said:
Jay said:
How can I get rid of the above Ugly "Error" Message if control reference is
empty? Below is a copy of a calculation at the report level which generates
an ugly "error", if the following/next to follow controls have no data.

=[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales]-[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales]

Can this be resolved easily? if it can, how? Please correct the above
calculation as an example.
Click to expand...


Well those names make the expression really ugly, but you
asked for it ;-)

=IIf([rpt_Product_BrandyInfoData_Collection_PrevDay].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales],
0) -
IIf([rpt_Product_BrandyInfoData_Collection_Daily].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales],
0)
Click to expand...
 
Probably a couple of ways to deal with suppressing the 0.00,
but which way depends on what you mean by "no data to
calculate against". Do you want to show nothing when either
value is missing or only when both are missing. How about
the case where both do have data, but the difference is
0.00?

For the sake of discussion, let's say the name of this text
box is txtDiff. To get the sum of the txtDiff column, add a
hidden text box named txtRunDiff next to it. Set its
control source expression to =txtDiff and set its RunningSum
property to Over All. Then a report footer text box can
display the total by using the expression =txtRunDiff
--
Marsh
MVP [MS Access]

It kind of worked, only, now I have 0.00 all the way down the column where
there is no data. What I'd like is to show is nothing, if there is no data to
calculate against. Is that possible?

Then, I'd like to sum the total of that column in the report footer. Is that
possible?

Jay said:
How can I get rid of the above Ugly "Error" Message if control reference is
empty? Below is a copy of a calculation at the report level which generates
an ugly "error", if the following/next to follow controls have no data.

=[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales]-[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales]

Can this be resolved easily? if it can, how? Please correct the above
calculation as an example.
Click to expand...
Click to expand...
Marshall Barton said:
=IIf([rpt_Product_BrandyInfoData_Collection_PrevDay].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_PrevDay].[Report]![txt_ActualOnHndSales],
0) -
IIf([rpt_Product_BrandyInfoData_Collection_Daily].[Report].HasData,
[rpt_Product_BrandyInfoData_Collection_Daily].[Report]![txt_ActualOnHndSales],
0)
Click to expand...
Click to expand...
 
Back
Top