Access between query

[nicorvp]

Hi, I'm trying to extract records from an access database. I pass a date to the query and i want to retrieve records where my date is between TASK_START_DATE Column and TASK_FINISH_DATE Column.

Dim queryString As String = "SELECT * FROM(MSP_TASKS) " & _
        "WHERE (@data BETWEEN TASK_START_DATE AND TASK_FINISH_DATE)  " & _
        "AND (TASK_IS_MARKED = TRUE)"

In the database dates have also time
e.g.
TASK_START_DATE= "08/08/2009 7.00.00"
TASK_FINISH_DATE = "08/12/2009 16.00.00"

if I pass a date e.g. "08/10/2009", I cant retrieve record starting "08/10/2009" unless starting exactly at 08/10/2009 0:0:0.

I thought to reset to 0:0:0 the time information from the db date, but how can I do it?

Thanks

 

[nicorvp]

Sorry, I see this is not the correct place to post this threads

 

[jmcilhinney]

Thread moved.

You should check the Jet SQL reference. I'm quite sure that there would be a function that would get only the date portion of a date/time value, so you'd call that function on your two columns.

 

[jmcilhinney]

Hmmm... just had a bit of a look and maybe there isn't such a function. You may have to use DatePart to pull out the year, month and day parts and then use DateSerial to turn those into a new date value.

 

[nicorvp]

Thanks for the reply.
I've tried, but I have a strange result that i'm not able to justify.
The query I used is

        Dim queryString As String = 
"SELECT * FROM(MSP_TASKS) " & _
"WHERE (@data BETWEEN DateSerial(datepart('y',TASK_START_DATE), datepart('m',TASK_START_DATE),datepart('d',TASK_START_DATE)) " & _
"AND DateSerial(datepart('y',TASK_FINISH_DATE), datepart('m',TASK_FINISH_DATE),datepart('d',TASK_FINISH_DATE)))  " & _
"AND (TASK_IS_MARKED = TRUE)"

For now i can retrieve the correct result with a for each on every row of the table.

Thanks again