I'm using Jet 4.0 and SQL to control an MS Access DB.

I have an sql statement.

SELECT Field1,
Field2,
Field3,
Expr1 AS ExprName1,
Expr2 AS ExprName2
FROM Tabel1,
Tabel2
WHERE Tabel1.PrimeKey = Tabel2.ForeignKey AND
Field2 BETWEEN '14/08/2001' AND '14/09/2001'

When i run it the server brings up an error "Invalid data type in condition statement". When i take out the part..

"Field2 BETWEEN '14/08/2001' AND '14/09/2001'"

.. it works fine and i have to restrict the date in the code (i'm using VB with SQL to access the data base).

does anyone know whats wrong? the code would be a lot simpler if the SQL statement could work.

TIA

Are you using the correct date format?

Also

try use

WHERE Field2=>'14/08/2001' and Field2 <= '14/09/2001'

Just and idea....



/WEBMASTER

I have all my dates set to DD/MM/YYYY and in the queries i use DD/MM/YYYY so what's wrong?

James

did you try to use

WHERE Field2=>'14/08/2001' and Field2 <= '14/09/2001'



/WEBMASTER

: did you try to use
:
: WHERE Field2=>'14/08/2001' and Field2 <= '14/09/2001'
:
:
:
: /WEBMASTER
:
:

Yes.

I really have no idea.

Is there a version of SQL which might not accept this? I'm using Jet 4.0.

Check the database helpfiles?

/WEBMASTER

Perhaps try reorder the date?

2001/08/14
2001-08-14



/WEBMASTER

Perhaps try reorder the date?

2001/08/14
2001-08-14



/WEBMASTER

You must use DataValue('YOURDATE') to convert the date to the correct DATATIME format...

(I've just used 50 minutes searching all of my resources + the internet for that one - and I found it in the help four Visual C++ under "SQL, query criteria")

The code that I'm working on right now works now :

SELECT * FROM Schedules WHERE Start >= DateValue('04/10/2001') AND End <= DateValue('11/10/2001')