.net + access: select.. where date>? + oledbparameter = datatype mi

[holzer]

hi all,

i'm using an access db from .net. i have the following sql:

SELECT tblReservation.ReservationID 
FROM tblReservation INNER JOIN tblRoomReservation 
ON tblReservation.ReservationID = tblRoomReservation.ReservationID 
WHERE tblRoomReservation.RoomID=? AND 
((tblReservation.CheckIn>=? AND tblReservation.CheckOut>=?) OR 
(tblReservation.CheckIn<=? AND tblReservation.CheckOut<=?) OR 
(tblReservation.CheckIn>=? AND tblReservation.CheckOut<=?) OR 
(tblReservation.CheckIn<=? AND tblReservation.CheckOut>=?))

The question marks get filled in with OleDbParameters. The CheckIn and CheckOut colums are of type date/time, so the value for the field of the parameter also gets a value of type System.Date.

oSql.AddParameter("tblRoomReservation.RoomID", oRoom.Id)
oSql.AddParameter("tblReservation.CheckIn", dtCheckIn)
oSql.AddParameter("tblReservation.CheckOut", dtCheckOut)
...etc

This has worked this way for other queries, in fact the data gets into that same column via the same method without a problem. The query works fine in access (when i change the ?'s of course), but when i run it from .net, i get a "Datatype mismatch in criteria expression" error.

In a desperate attempt to fix all this, I even tried to change all ? into #?# and add the parameters a Strings, i.e. dtCheckIn.ToLongDateString, etc.. This gives me a "Syntax error in date in query expression"

Anyway, I'm getting a feeling this is because i try to compare the dates with > and < instead of plain =, but that's not what I need so.... As I said, it works in Access, so it must be possible. If someone could help me out here, that'd be really nice..