SQL Help - Why Does This Not Work?

[egun]

I'm experimenting with OLEDB and SQL, and the simple query I'm trying to
perform doesn't work:

Dim startDate As Date, endDate As Date
Dim conData As New ADODB.Connection
Dim rstAssigns As New ADODB.Recordset
Dim strSelect As String
'
startDate = "4/1/2005"
endDate = "5/25/2012"
'
(Open connection to a Project file here - this part works fine)
'
strSelect = _
"SELECT TaskID, TaskName, TaskStart, TaskFinish FROM Tasks WHERE "
& _
"TaskStart >= '" & Format(startDate, "yyyy-mm-dd") & _
"' AND " & _
"TaskStart <= '" & Format(endDate, "yyyy-mm-dd") & _
"' ORDER BY TaskID ASC"

rstAssigns.CursorLocation = adUseClient
rstAssigns.CursorType = adOpenStatic
rstAssigns.Open strSelect, conData

The Open command returns 0 records every time, though I KNOW there are lots
of records between the two input dates. I have tried using BETWEEN also,
with the same results (or lack thereof), and many variations of the above.
Other queries, such as 'TaskID BETWEEN 5 and 50', work just fine. What is
wrong with my approach? I'm sure it has something to do with selecting on
dates, I just can't figure it out.

Thanks,

Eric

 

[JackD]

Maybe something to do with the date format. It is stored in the DB as
DateTime. Doesn't Format return a string?
Try just using startDate without the Format function.

 

[Rod Gill]

startDate = "4/1/2005"
Is this line working? Try:
startDate = cDate("4/1/2005")

Later versions of VBA will fail the assignment so it's always safer to
convert the data types explicitly (EG Use cDate or cInt).

Try also the date format: yyyymmdd without the "-"

If that doesn't work, can you put a breakpoint in and post the value of
strSelect after executing the statement? That may also provide some clues.

You may well also need to add to the Where clause a test for the correct
Proj_Id and note that TaskId=0 represents the Project Summary task.

Hang on, aren't your table and field names wrong?

Try:
strSelect = _
"SELECT Task_ID, Task_Name, Task_Start_Date, Task_Finish_Date FROM
MSP_Tasks WHERE " & _
"Task_Start_Date >= '" & Format(startDate, "yyyy-mm-dd") & _
"' AND " & _
"Task_Start_Date <= '" & Format(endDate, "yyyy-mm-dd") & _
"' ORDER BY Task_ID ASC"

 

[egun]

Rod and Jack,

According to the documentation in the 'PJOLEDB.HTM' file, I'm using the
correct table names and field names. However, I'll try your suggestion just
in case.

Also, I've tried lots of variations on putting the dates into the selection
string, including as a literal, using startDate, using cDate, etc, all with
the same result.

I'll try a few more things on Monday, and I will post the value of
strSelect. Maybe I'm not seeing something that will be obvious to another
pair of eyes.

Thanks again,

Eric