Solved

Date Range in Form

Posted on 2004-10-21
317 Views
Last Modified: 2013-12-24
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.

      
0
Question by:Lee R Liddick Jr
    5 Comments
     
    LVL 35

    Expert Comment

    by:mrichmon
    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
     
    LVL 35

    Accepted Solution

    by:
    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
     

    Author Comment

    by:Lee R Liddick Jr
    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
     

    Author Comment

    by:Lee R Liddick Jr
    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
     
    LVL 35

    Expert Comment

    by:mrichmon
    no problem.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    909 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now