divide by 0 error

[Bebe]

why is this causing a divide by 0 error???

update #totals
set units_cancel_percent = units_sent/units_cancelled

in my temp table I am trying to divide the results of these two colums by each other in an update statement; how can I do this????

[kedaman]

units_cancelled is probably 0 then...

[Bebe]

i don't understand meanig it was trying to divide by 0 how can i work around this problem????

[wittmsv]

If your units_cancelled variable could be zero, you need to use an if_then statement to check it before the computation.

If units_cancelled = 0 then
units_cancel_percent = 0
else
set units_cancel_percent = units_sent/units_cancelled

[BruceG]

You need to use an expression in your update statement. I know that in Access SQL (Jet), you can use VB functions (such as iif) within the SQL statement, such as this:

update #totals
set units_cancel_percent =
iif(units_cancelled = 0, 0, units_sent/units_cancelled)

If you are using SQL Server or other database, look into the allowable functions. I'm looking at a SQL Server book right now, and I see that you can do something like this:

update #totals
set units_cancel_percent =
case units_cancelled
when 0 then 0
else units_sent/units_cancelled
end

I hope this was helpful.

[Bebe]

this seems to work:

update #totals
set units_shipped_day1_percent = units_sent/units_shipped_day1
where units_shipped_day1 <> 0
and units_shipped_day1 is not null

but how do i get the results to return a percentage??? and have the % on it??????

Let me know if there is a better way to code this too????

thanks

[kedaman]

set units_shipped_day1_percent = 100*units_sent/units_shipped_day1 & "%"

[Bebe]

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '%' to a column of data type int

get this error anyway around it???

[Bebe]

does anyone know how to get his to be a percentage - converted to a char ? and add the % on to it?????

THANKS!!!

[FrancisC]

I don't know much about Jet Databases but why don't you store the data as a decimal, and just convert when you retrieve and want to display your data... ?

[Bebe]

okay so the column is a decimal how do i then convert the syntax can you give an example thanks

[FrancisC]

You need to use the 'Format' function.

For instance, if you have your value in a Variable called dTemp, you can diplay it in a MsgBox like that:

dTemp = 0.003521
MsgBox "You precentage is: " & Format(dTemp, "#0.000%")

Yopu can experiment with the format itself to find what suits your needs.

[Al Smith]

Hi,
I think your formula is wrong. It should be:

set units_cancel_percent = (units_cancelled/units_sent)*100

This will give you the percent of cancelled orders.
e.g.
250 units sent
25 units cancelled
25/250 = 0.1
0.1*100 = 10
10% of the units were cancelled

The only time you'd get a divide-by-zero error is if units_sent was zero.

Al.