7 Replies Latest reply on Aug 18, 2009 7:21 PM by djkhalif

    Calculations

    djkhalif Level 1

      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.

        • 1. Re: Calculations
          Dan Bracuk Level 5

          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.

          1 person found this helpful
          • 2. Re: Calculations
            djkhalif Level 1

            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.

            • 3. Re: Calculations
              Dan Bracuk Level 5

              cfdump your form scope.  The problem should be obvious.

              • 4. Re: Calculations
                djkhalif Level 1

                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.

                • 5. Re: Calculations
                  djkhalif Level 1

                  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.

                  • 6. Re: Calculations
                    djkhalif Level 1

                    <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.

                    • 7. Re: Calculations
                      djkhalif Level 1

                      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