help fixing a divide by zero error

I am very new to using SQL Server 2005. I have used Microsoft Access in the
past for my queries and reports.
I have the following calculation will give me the divide by zero error
message and not show the affected records.
The calculation is used to calculate how much footage (in feet) different
materials will give me.
The formula that I use is (material thickness * material width * 12 *
density) I have the density set up as a parameter to allow me to enter the
density of the particular material I am looking up.
The problem that I have is that on some material thickness and material
width may have either a 0 or may be blank.
I want the output field for the calculation to show a 0 for the calculated
footage, or something to show me that this record is not calculated but still
shows up on the report.
Thank you in advance for any help.
--
joef1946On Nov 3, 2:42 pm, joef1946 <joef1...@.discussions.microsoft.com>
wrote:
> I am very new to using SQL Server 2005. I have used Microsoft Access in the
> past for my queries and reports.
> I have the following calculation will give me the divide by zero error
> message and not show the affected records.
> The calculation is used to calculate how much footage (in feet) different
> materials will give me.
> The formula that I use is (material thickness * material width * 12 *
> density) I have the density set up as a parameter to allow me to enter the
> density of the particular material I am looking up.
> The problem that I have is that on some material thickness and material
> width may have either a 0 or may be blank.
> I want the output field for the calculation to show a 0 for the calculated
> footage, or something to show me that this record is not calculated but still
> shows up on the report.
> Thank you in advance for any help.
> --
> joef1946
The divide by zero error usually only occurs when dividing by zero.
You should be able to avoid the error if you use a conditional
statement/expression similar to this where you are doing the
calculation (in the report field/cell).
=iif(Fields!MaterialThickness.Value is Nothing or Fields!
MaterialThickness.Value = 0 or Fields!MaterialWidth.Value is Nothing
or Fields!MaterialWidth.Value = 0 or Parameters!Density.Label is
Nothing or Parameters!Density.Label = 0, 0, (Fields!
MaterialThickness.Value * Fields!MaterialWidth.Value * 12 * Parameters!
Density.Label))
This expression basically says if any of the variables or the
parameter are zero or null/empty, set the calculation to zero,
otherwise, set the calculation to the formula with the values
provided. Of course, if you are truly dividing by zero, use a similar
expression that checks for the denominator/bottom of the fraction
variable to see if it is zero or null/empty. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql