?
Solved

Problem with CreateODBCDate Function

Posted on 2008-11-12
5
Medium Priority
?
354 Views
Last Modified: 2013-12-24
I am using CF8 and an Access 2000 database that contains the following fields:

 - ID (autonumber)
 - EventDate2 (date/time)
 - EventTime (text)
 - School (text)
 - Event (memo)
 - Description (memo)

My form page contains 3 form fields, the School (drop down), the start date (text) and the end date (text).
Whenever I try to query my database by school AND between the 2 date ranges, I get no results, and yet there are plenty of dates in the database that meet the search requirements. Here is my SQL statement.

<cfquery name="q_Cal" datasource="#DataSource#">
  Select *
  FROM TBL_Cal
  WHERE Location = '#SchoolDrop#' AND EventDate2 BETWEEN #startdate# and #enddate#
</cfquery>

From some reading I have done, it appears that I need to convert my dates to ODBC compatible dates using the CreateODBCDate function. But this function takes 3 parameters (year, month, day) and my date fields are in short date formats, e.g. 01/01/2008. How can I convert my dates to ODBC format using only the short date format?

Ken
0
Comment
Question by:kenjpete
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 22943471
Well if your date lives in dateVar ie

dateVar = "31/12/2008" (ie DD MM YYYY)

You can just pull apart the string

createODBCDate(right(dateVar, 4), mid(dateVar, 4, 2), left(dateVar, 2))
0
 
LVL 7

Accepted Solution

by:
black0ps earned 2000 total points
ID: 22943692
0
 

Author Comment

by:kenjpete
ID: 22943777
I'm not sure how that would look in my SQL statement? Can you be more specific?
0
 

Author Closing Comment

by:kenjpete
ID: 31516084
Thank you....that was my mistake, I was looking CreateODBCDate but using CreateDate, which does require 3 parameters.
0
 
LVL 7

Expert Comment

by:black0ps
ID: 22943883
<cfquery name="q_Cal" datasource="#DataSource#">
  Select *
  FROM TBL_Cal
  WHERE Location = '#SchoolDrop#' AND EventDate2 BETWEEN #CreateODBCDate(startdate)# and #CreateODBCDate(enddate)#
</cfquery>

I use MS SQL. I made the switch some time ago so its been a while since I've used access scripting. You might need to encapsulate the dates with single quotes. Give each one a try. You might also consider trying the following:  

<cfquery name="q_Cal" datasource="#DataSource#">
  Select *
  FROM TBL_Cal
  WHERE Location = '#SchoolDrop#' AND EventDate2 > #CreateODBCDate(startdate)# and EventDate2 < #CreateODBCDate(enddate)#
</cfquery>
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website 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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

840 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