Connection open statement not working

[lwilly]

I have a select statement that I am trying to open but it never makes it past the recordset open command. Here is the function that is causing me grief.

Public Function DeptTimes()
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Set rst = New ADODB.Recordset

Dim strSQL As String
Dim dteInDate As Date
Dim dteOutDate As Date

strSQL = "SELECT * FROM [qryTrackingData_EndTimes]"
strSQL = strSQL & "WHERE [UserID] = '" & Me.UserID & "'"
strSQL = strSQL & " AND [SystemDate] >= " & "#" & Me.Indate & "#"
strSQL = strSQL & " ORDER BY qryTrackingData_EndTimes.SystemDate"
rst.Open strSQL, cnn, , , adOpenStatic 'Problem here

rst.MoveLast
If rst.RecordCount < 2 Then
DeptTimes = 0
Else
rst.MoveFirst
dteInDate = rst![SystemDate]
rst.MoveNext
dteOutDate = rst![SystemDate]
DeptTimes = rst![SystemDate]
End If
Set rst = Nothing
End Function

If anyone can give me a clue what I am doing wrong I would greatly appreciate it.

 

[Remou]

You do not seem to have seet the connection to anything and you are short of a space in the SQL statement.

strSQL = "SELECT * FROM [qryTrackingData_EndTimes][COLOR=red yellow]<space>[/color]"
strSQL = strSQL & "WHERE [UserID] = '" & Me.UserID & "'"
strSQL = strSQL & " AND [SystemDate] >= " & "#" & Me.Indate & "#"
strSQL = strSQL & " ORDER BY qryTrackingData_EndTimes.SystemDate"
rst.Open strSQL, cnn, , , adOpenStatic 'Problem here

If this is an Access database, CurrentProject.Connection may suit. Otherwise, there is

http://www.connectionstrings.com/

 

[lwilly]

Set statement is the third line and space does not seem to make a difference. When code runs it runs through the connection open and then loops back up to where the sql statement is created.

 

[Remou]

Sorry, but I still cannot see where you have, for example:

[tt]Set cnn=CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Docs\Tek-Tips.mdb;"[/tt]

 

[traingamer]

You are setting the recordset (rst), but you are not setting the connection (cnn) (at least, not in the code you provided).

You should have something like

Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lwilly]

Sorry, I did not get Remou first post but you are both correct.

Thank you