Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date Range in Form

Posted on 2004-10-21
5
Medium Priority
?
325 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
Comment
Question by:Lee R Liddick Jr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 12372398
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:
mrichmon earned 200 total points
ID: 12372422
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
ID: 12372440
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
ID: 12372478
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
ID: 12372493
no problem.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

596 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