please help Converting Access SQL statement to work with CF

Hi,

I'm not very famililar with Access but I have to do this for work and I'm kinda stuck on this for a while. I have an SQL statement that worked fine in Access but then when I executed it in CF it didn't work because of the #'s that was surrounding the Dates. I've tried using single quotes around the dates but that didn't seem to solve it.

My SQL statement pulled from Access is

SELECT tblClassExams.StudentID, tblClassExams.Score, tblClassExams.Grade, tblExams.ExamName, tblExams.Date, tblExams.FullScore, tblStudents.LastName, tblStudents.FirstName
FROM ((tblExams INNER JOIN tblClassExams ON tblExams.ExamID = tblClassExams.ExamID) INNER JOIN tblStudents ON tblClassExams.StudentID = tblStudents.StudentID) INNER JOIN tblRegistrarFiles ON tblStudents.StudentID = tblRegistrarFiles.StudentID
WHERE (((tblClassExams.Grade)<70) AND ((tblExams.Date) Between #10/1/04# AND #11/1/04#))
ORDER BY tblStudents.LastName, tblStudents.FirstName, tblExams.Date;


and the error CF returned is
Error Diagnostic Information

Just in time compilation error

Invalid parser construct found on line 15 at position 65. ColdFusion was looking at the following text:10Invalid expression format. The usual cause is an error in the expression structure.

The last successfully parsed CFML construct was a CFQUERY tag occupying document position (12:1) to (12:60).

The specific sequence of files included or processed is:
D:\web\aihsmis\dsp_mandatoryTutoring.cfm

if I change the #s to 's then the error is
ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


Any suggestions is appreciated. Thanks.

Try removing pound signs (and not adding single quotes) or using the createODBCDate() function on the dates, and also remove the semicolon at the end.

Hi,

Thanks for replying. When I removed the # around the dates and the ; and I do
<cfdump var="#studentsNeedingTutoring#">
it seems to return an empty query ( just the column headings) Then I tried to add the createODBCDate function in:

WHERE (((tblClassExams.Grade)<70) AND ((tblExams.Date) Between createODBCDate(10/1/04) AND createODBCDate(11/1/04)))

I think that's the right format, is it? and the error that gets returned is

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'createODBCDate' in expression.

SQL = "SELECT tblClassExams.StudentID, tblClassExams.Score, tblClassExams.Grade, tblExams.ExamName, tblExams.Date, tblExams.FullScore, tblStudents.LastName, tblStudents.FirstName FROM ((tblExams INNER JOIN tblClassExams ON tblExams.ExamID = tblClassExams.ExamID) INNER JOIN tblStudents ON tblClassExams.StudentID = tblStudents.StudentID) INNER JOIN tblRegistrarFiles ON tblStudents.StudentID = tblRegistrarFiles.StudentID WHERE (((tblClassExams.Grade)<70) AND ((tblExams.Date) Between createODBCDate(10/1/04) AND createODBCDate(11/1/04))) ORDER BY tblStudents.LastName, tblStudents.FirstName, tblExams.Date"

Data Source = "AIHS_NEW"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (12:1) to (12:60) in the template file D:\web\aihsmis\dsp_mandatoryTutoring.cfm.

If the query is returning an empty recordset and just the headings, then it sounds like the query is running correctly and just not returning any results. Can you guarantee that the query should be returning results given the input?

That said, your use of the function DOES need pound signs around it...

#createODBCDate( '10/1/2004' )#

Hi Kiteless,

amazingly after I put #createODBCDate('10/1/04')# it works .... thanks so much

JD