Calculations

[djkhalif]

I attempting to convert a statement from Access to SQL using CF:

Private Sub Production_Delays_Exit(Cancel As Integer)
Me.ProdDelayTime = Nz(DSum("[DelayTime]", "qry_AssyDelay_Info", "ID = " & Me.ID & " And Type = '" & "Run" & "'"))
Me.CoDelayTime = Nz(DSum("[DelayTime]", "qry_AssyDelay_Info", "ID = " & Me.ID & " And Type = '" & "CO" & "'"))
Me.NpTime = Nz(DSum("[DelayTime]", "qry_AssyDelay_Info", "ID = " & Me.ID & " And Type = '" & "NP" & "'"))
End Sub

As far as I get is:

<cfset FORM.ProdDelayTime = IsNull(SUM[FORM.DelayTime]>

<cfset FORM.CoDelayTime = IsNull(SUM[FORM.DelayTime]>

<cfset FORM.NpTime = IsNull(SUM[FORM.DelayTime]>

This should be calculate FORM.ProdDelayTime when  DelayTime Type is RUN etc. I have eliminated the qry_Assembly_Info i order to use:

<cfquery name="rsDelayCodes" datasource="atDat">
SELECT *
FROM dbo.tlkp_Assembly_DelayCode
ORDER BY Code ASC
</cfquery>

Should I use something like this:

<cfif rsDelayCodes.Codes = RUN>
<cfset FORM.ProdDelayTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60) >
<cfelseif rsDelayCodes.Codes = CO>
<cfset FORM.CoDelayTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60)>
<cfelseif rsDelayCodes.Codes = NP>
<cfset FORM.NpTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60) >
</cfif>

Does anyone know if I am close or in the ballpark with this one? However, if neither exists then set to zero.

[Dan_Bracuk]

You say,

"

As far as I get is:

<cfset FORM.ProdDelayTime = IsNull(SUM[FORM.DelayTime]>

<cfset FORM.CoDelayTime = IsNull(SUM[FORM.DelayTime]>

<cfset FORM.NpTime = IsNull(SUM[FORM.DelayTime]>

This should be calculate FORM.ProdDelayTime when  DelayTime Type is RUN etc"

It should throw an error because you are trying to use sql functions in cfml.

As far as converting your function goes, I don't really understand what it does.

As far as this goes,

<cfset FORM.ProdDelayTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60) >

You are better off calculating everything in minutes and converting to another format as necessary.  Doing it your way will create floating point math and rounding errors.

[djkhalif]

I tried this:

<cfif FORM.Code IS "RUN">
<cfset FORM.ProdDelayTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60) >
<cfelseif FORM.Code IS "CO">
<cfset FORM.CoDelayTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60)>
<cfelseif FORM.Code IS "NP">
<cfset FORM.NpTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60) >
<cfelseif FORM.Code IS "ND">
<cfset FORM.DelayTime = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60) >
</cfif>

I changed my <cfselect> from dynamic to static but now when I run it I get this error:

Conversion failed when converting the varchar value 'NP' to data type smallint.

[Dan_Bracuk]

cfdump your form scope.  The problem should be obvious.

[djkhalif]

I wish it were obvious. I just tried this:

<cfset RUN = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60)>
<cfset NP = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60)>
<cfset CO = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60)>
<cfset ND = (FORM.DelayTimeHours + FORM.DelayTimeMinutes/60)>

<cfif FORM.Code IS "RUN">
<cfset FORM.ProdDelayTime = RUN >
<cfelseif FORM.Code IS "CO">
<cfset FORM.CoDelayTime = CO>
<cfelseif FORM.Code IS "NP">
<cfset FORM.NpTime = NP >
<cfelseif FORM.Code IS "ND">
<cfset FORM.DelayTime = ND >
</cfif>

I think I am missing a FORM.Code on my action page.

[djkhalif]

RUN =[empty string], CO= [empty string]4.83333333333, ND=[empty string]

This is the results of cfdump after calculating NP. I need to push zeroes.

[djkhalif]

<cfset FORM.Code = "" >

<cfif FORM.Code IS "RUN">
<cfset FORM.ProdDelayTime = (FORM.DelayTimeHours + (FORM.DelayTimeMinutes/60))>
</cfif>

RUN is the value selected from a dropdown list. Is this possible? I want to INSERT based on this calculations.

[djkhalif]

I set this and I can get 0:

<cfset FORM.Code = "">
<cfif IsDefined("FORM.Code") IS "RUN">
<cfset FORM.ProdDelayTime = (FORM.DelayTimeHours + (FORM.DelayTimeMinutes/60))>
<cfelseif Not IsDefined(#FORM.Code#) IS "RUN">
<cfset FORM.ProdDelayTime = 0>
</cfif>

<cfform method="post">

<cfselect name="Code">
<option value="RUN">Produce</option>
<option value="NP">Do Not Produce</option>

<option value="RUN">Ship</option>
</cfselect>

</cfform