Why isn't this query working...

[JasSim]

This queries a msaccess 2000 database full of records. I haven't ever gotten any records to return from this query though.???? Does anyone know what is happening, as I am new to crystal. This is in the Crystal SQL designer. This is what I type in to the parameters (sdate= 7/7/01, edate= 9/9/01) Thanks!

SELECT
Orderlog.`Order`,
Orderlog.`Proc`,
Orderlog.`OpenDate`
FROM
`Orderlog` Orderlog
WHERE
Orderlog.`OpenDate` BETWEEN CDate({?sDate}) AND CDate({?eDate})
ORDER BY
Orderlog.`Order` ASC

 

[IdoMillet]

What is the Value type you had set for the two parameter fields?

Also, what is the data type of Orderlog.`OpenDate` ?

Cheers,
- Ido ixm7@psu.edu

 

[JasSim]

Sorry...
The data type of OpenDate is Date/Time.
String data types for the parameters.

 

[IdoMillet]

Cdate() returns a date type, not DateTime.

Try to convert the parameter string value
to a DateTime type.

Cheers,
- Ido
ixm7@psu.edu

 

[JasSim]

This is the new query:
SELECT
Orderlog.`Order`,
Orderlog.`Proc`,
Orderlog.`OpenDate`
FROM
`Orderlog` Orderlog
WHERE
Orderlog.`OpenDate` BETWEEN CDateTime({?sDate}) AND CDateTime({?eDate})
ORDER BY
Orderlog.`Order` ASC

This is the error message I receive:
ODBC Error:[Microsoft][ODBC Microsoft Access Driver]Undefined Function `CDateTime` in expression.

Thanks for your help, I am trying to become more familiar with Crystal

 

[Ngolem]

CDateTime() is not an oracle or Access query funtion to my knowledge

if OpenDate is a datetime field then set your parameters to be datetime type or use the Access equivalent to an Oracle To_Date function to convert a string to a datetime