date comparison in cfquery

(using ms access database)

hi, i have the following cfquery:

<cfset currentdate = Now()>

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">      
      SELECT * FROM Leads
      WHERE status = 'Received By Dealer'
      ORDER BY ID DESC            
</cfquery>

i am trying to change this cfquery so it also does a date comparison with the 'date_submitted' field that is in this 'Leads' table.

specifically, i am trying to have it check when status equals 'Received By Dealer' AND the current date (i.e 'currentdate') is not greater than
24 hours after the date it was submitted (i.e 'date_submitted').

how do i add this?

Judy DeoAsked:
Who is Participating?
 
mkishlineConnect With a Mentor Commented:
try:

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() BETWEEN date_submitted AND DATE_ADD(date_submitted, INTERVAL 24 HOUR)
     ORDER BY ID DESC          
</cfquery>
0
 
mkishlineCommented:
<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DATE_ADD(date_submitted, INTERVAL 24 HOUR)
     ORDER BY ID DESC          
</cfquery>
0
 
Judy DeoAuthor Commented:
got errors with this cfquery...Date_Add was expecting 3 arguments and the '<' sign seems to be causing a problem since it thinks its a beginning bracket for tags...

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DATE_ADD(date_submitted, INTERVAL 24 HOUR)
     ORDER BY ID DESC          
</cfquery>

so i changed the cfquery to this....

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DateAdd(h, 24, 'date_submitted')
     ORDER BY ID DESC          
</cfquery>

but getting the following error:

Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.



0
 
Judy DeoAuthor Commented:
correction:

so i changed the cfquery to this....

<cfquery name="GetLeads" datasource="dsname" dbtype="ODBC">    
     SELECT *
     FROM Leads
     WHERE status = 'Received By Dealer'
     AND Now() < DateAdd(h, 24, date_submitted)
     ORDER BY ID DESC          
</cfquery>
0
 
Judy DeoAuthor Commented:
ah sorry forgot the single quotes around h in:

 AND Now() < DateAdd('h', 24, date_submitted)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.