Access between query

nicorvp

Member
Joined
May 3, 2009
Messages
24
Location
Italy
Programming Experience
5-10
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.
VB.NET:
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
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
May 3, 2009
Messages
24
Location
Italy
Programming Experience
5-10
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
VB.NET:
        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
 
Top Bottom