Date Range in Form

I know I'm missing something here...I have a CF form that I want the user to be able to input a date range and then the query results will return the data between that range.  This is the part that is not functioning....what am I doing wrong?

It's an ACCESS dbase and the fields are date/time in the table.  The form looks like this:
      _
     |_| Checkbox - ___________________ to ____________________

The code looks like this:

    <CFIF IsDefined('form.DateRequestedRange') And CompareNoCase(form.DateRequestedRange,"No") NEQ 0>
         AND DateRequested >= LIKE <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.DateRequestedFrm#%"> AND <= LIKE <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#form.DateRequestedTo#%">
    </CFIF>

If the user inputs this into the form:
     _
    |X| 10/01 to 10/30

The the results should be anything in the table that has a request date from 10/01 to 10/30.

      
Lee R Liddick JrReporting AnalystAsked:
Who is Participating?
 
mrichmonConnect With a Mentor Commented:
ALso no % allowed in dates and dates into Access need # around them which means either use cf_sql_date or escape enough # to get them sent in to access

Try:

AND (DateRequested BETWEEN <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.DateRequestedFrm#"> AND <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#form.DateRequestedTo#">)
0
 
mrichmonCommented:
You cannot use like to compare a date.

You can use <= or you can use datediff or you can use between with access, but not <= LIKE
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
If I take the LIKE out, then I get a syntax error:

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ID=ID AND DateRequested >= Pa_RaM000 AND <= Pa_RaM001'.  
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Forget my last post...I got the error not using the code suggested.  I just tried that code and it worked.  Excellent!  I knew it was something simple.  Thank you once again mrichmon!
0
 
mrichmonCommented:
no problem.
0
All Courses

From novice to tech pro — start learning today.