SIMPLE SQL DRIVING ME CRAZY

[JJK]

i am using dao in some simple in line sql. what I am trying to do is create a recordset of two fields with the condition that the date field falls between two dates. in the db the date field IS declared as a date. Here is my sql string:

SELECT Hours, PayDte
FROM EmployeeHours
WHERE PayDte
BETWEEN 'somedate' AND 'somedate'

pretty simple, however i get a data type mismatch error when i execute this. its is driving me insane and i am stuck until i figure it out. thanks in advance for any suggestions

jj

[HeSaidJoe]

sql = "SELECT hours,paydte From EmployeeHours Where PayDte< DateValue('12/31/99')and payDte < DateValue('12/31/00');"

[JJK]

THANK YOU, THANK YOU, THANK YOU

[Wen Lie]

Well JJK,

Does it work ???
You can also eliminate the DateValue() function, if you're using SQL Server Database, but if you're using Ms-Access database (with DAO), you can't eliminate the DateValue() function.
coz, I've tried couple of months ago that datevalue() function means nothing at SQL statement for SQL Server Database.
You still can access the Date Field without writing datevalue() function....

But, for your information, I'm using SQL Server Database version 7.0

Cheers,
Wen Lie

[JJK]

Yes it does work. Thank you. I am using MS-Access DB right now. We started this project about a year ago. We are finally get up to speed and are switching to ado and sql server 7.0.(Thank God) However, it is a slow process switching over. I am continuing to use the access db for now until we get the sql server db laid out. Then we will start writing the stored procedures. I now remember something about the datevalue statement, but it did not occur to me earlier.

Thanks, JJK