2 Replies Latest reply on May 2, 2015 3:50 AM by BKBK

    filtering results based on dynamic date in coldfusion and access database

    stinasius

      Hello Guys, i am using a cfquery and am trying to return results based on date. the idea is to return entries that are less than or equal to date but am not getting any results but when i change to greater than or equal to i get the all results in databse which is wrong.

       

      Below is my code. is there something am doing wrong? Please help.

       

      <cffunction name="getBal" access="remote">

       

      <cfargument name="ID_biodata" type="string" required="true">

              <cfargument name="quater" type="string" required="true"/>

              <cfargument name="datePaid" type="date" required="true"/>

             

              <cfquery name="qBal" datasource="bond100">

      SELECT ID_biodata, quater, datePaid, amountPaid, manFees

      FROM collections

      WHERE datePaid <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#parseDateTime(arguments.datePaid)#">

                  AND ID_biodata = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ID_biodata#">

                  AND quater = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.quater#">

                  ORDER BY datePaid

      </cfquery>

              <cfquery dbtype="query" name="results">

              SELECT SUM(CAST(amountPaid as INTEGER) + CAST(manFees as INTEGER)) AS totalPaid

                  FROM qBal           

              </cfquery> 

              <cfreturn results />

          </cffunction>