Division by 0

G

Guest

I am having a problem with a calculated field tring to divide by 0. Here is the code I am using

Dim db As Databas
Dim qdfAction As QueryDe
Dim sSELECT As Strin

Set db = CurrentD

sSELECT = "SELECT tblDataTable.Date, tblDataTable.Shift, tblDataTable.Dept, tblDataTable.AvailableTime, tblDataTable.Downtime, tblDataTable.StandardRate, tblDataTable.Parts, tblDataTable.DefectiveUnits, ([AvailableTime]-[downtime]/[AvailableTime]) AS availability, ([parts]*(1/([StandardRate]/(1-0.18))))/([AvailableTime]-[downtime]) AS performance, ([parts]-[DefectiveUnits])/[parts] AS Quality, [availability]*[performance]*[quality] AS oee" &
" FROM tblDataTable " &
"ORDER BY tblDataTable.Date, tblDataTable.Shift, tblDataTable.Dept
sSQL = sSELEC
Set qdfAction = db.CreateQueryDef("qrySQL", sSQL

qdfAction.Clos

DoCmd.OpenQuery "qrySQL

The problem lies in the calcuated field "Availability" because tblDataTable.Parts can be 0, not allowing 0 is not an option, nor can you update a calculated field

I not quit sure how to handle this problem. Any suggestions would be appreciated

Thank

Mar
 
M

Mark Burns

Mark,

Try replacing "([AvailableTime]-[downtime]/[AvailableTime]) AS availability"
with:

"iif([AvailableTime]<>0, ([AvailableTime]-[downtime]/[AvailableTime]), 0) as
Availability"

- Mark (another Mark, that is)

Mark said:
I am having a problem with a calculated field tring to divide by 0. Here is the code I am using:

Dim db As Database
Dim qdfAction As QueryDef
Dim sSELECT As String

Set db = CurrentDb


sSELECT = "SELECT tblDataTable.Date, tblDataTable.Shift,
Click to expand...
tblDataTable.Dept, tblDataTable.AvailableTime, tblDataTable.Downtime,
tblDataTable.StandardRate, tblDataTable.Parts, tblDataTable.DefectiveUnits,
([AvailableTime]-[downtime]/[AvailableTime]) AS availability,
([parts]*(1/([StandardRate]/(1-0.18))))/([AvailableTime]-[downtime]) AS
performance, ([parts]-[DefectiveUnits])/[parts] AS Quality,
[availability]*[performance]*[quality] AS oee" & _
" FROM tblDataTable " & _
"ORDER BY tblDataTable.Date, tblDataTable.Shift, tblDataTable.Dept"
sSQL = sSELECT
Set qdfAction = db.CreateQueryDef("qrySQL", sSQL)

qdfAction.Close

DoCmd.OpenQuery "qrySQL"


The problem lies in the calcuated field "Availability" because
Click to expand...
tblDataTable.Parts can be 0, not allowing 0 is not an option, nor can you
update a calculated field.
 
Top