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

Help desperately needed with a quick question about DDQ queries!

Hi,
I posted a question last w, trying to find out if anyone knew
whether, in a Data Driven Query script, it's possible to do the
following:
IF (condition 1) THEN
.
.
Main = DTSTransformstat_UpdateQuery
Main = DTSTransformstat_InsertQuery
END IF
i.e. to call two queries consecutively if a specific condition is
satisfied.
I decided to press ahead and just hope that this would work...however,
it appears that unforrtunately it doesn't work!!
I would be so grateful if anyone knows any way of either getting the
script to execute both queries as above...or, alternatively, if anyone
knows of any way I can have two query statements in the one 'query' (or
another possible way of maybe doing this)?
DTS has such a rich programmable interface in every other way, I cannot
believe that there's no way of achieving this one simple requirement!
Thank you so, so much in advance for any help or pointers anyone can
provide.
CSSUCCESS! You can do it the second way - you can have two queries in the
same 'Query' window/area/function/call-it-what-you-like.

Help again edit parameters from MDB to ADP

I need to convert this from Jet SqL to SQL Server. Moving out of an MDB to an ADP some of the queries I need to change over, I inherited this database and the bosses want all databases moved over from MDB to ADP.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));

1. For FORMAT$, look in SQL BOL for Convert. Something like Convert(Varchar(7), [Main Table].[Date]

2. For your parameter ([Enter the Month and Year]), you will likely have to create the query as a stored procedure with an input parameter (or two). Something like:

CREATE PROC spMyProc (@.MonthYear varchar(7) )

AS

SELECT ...
FROM
WHERE
MyDateString = @.MonthYear

You would have to validate that @.MonthYear was valid (like mm/yyyy). I would not necessarily do it this way. I would use one of the following options:

1. Declare the incoming parameter as a datetime and calculate the first and last day of the month. Then use two local variables and a BETWEEN statement. This should render the best performance for large amounts of data.

2. or Declare the incoming parameter as two smallint variables. Then you could use DatePart function twice to get month and year.

Regards,

Hugh Scott

HELP

SQL will not allow me to create a secondary file group that replaces the
primary. I have no space to run queries.
--
Regards,
Jamie
"thejamie" wrote:

> When we setup SQL 2005, it was setup to house the log files on a larger
> drive. It was not setup to house the tempdb files when they grow to a la
rge
> size. We are running out of disk space on the C:\ drive. Is it possible
to
> move this without reinstalling SQL Server 2005? For that matter, if I mus
t
> reinstall, can the tempdb be placed in an alternate location (other than t
he
> C drive)?
> --
> Regards,
> JamieHey, I am also facing the similar problem, how can I resolve this, please I
need your urgent response.
I am working on this now.. I got this problem as a result of migrating the d
ata from one data base to another back up database.
From http://www.developmentnow.com/g/118...467001/HELP.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com