[RESOLVED] [Access 2003] ADO recordset has no records, same Sql in query builder has 6

[Ecniv]

Hi,

I've come across a problem.

I can guess what is wrong, but cannot find what is wrong.

The Problem (not an issue; a problem!) :
I create an sql statement in a string.
I open an adodb.recordset
it returns no records
The same sql statement copied and pasted to the query builder returns the records I expected.

I am guessing that there is a function or chunk of code wrong somewhere in the whole mdb, BUT it debugs fine.

I had a similar problem before like this and it turned out to be a wrongly named function in a completely different part of the db. It still compiled and raised no errors.

Below is the code for this. I have changed the sql statement to a simplistic one, but it is still not working.

Anyone come across this and know how to find the problem/fix it?

Private Sub btnView_Click()
    
    Dim rst As ADODB.Recordset
    Dim strSql As String, strWhere As String
    
    Dim objExcel As Object, wrk As Object, sht As Object, rng As Object
    
    Dim lngLoop As Long
    
    On Error Resume Next
    
    
    strSql = "Select tblLCasesLiveTasks.* from tblLCasesLiveTasks"
    
    Debug.Print strSql
    
    Set rst = New ADODB.Recordset
    rst.Open strSql, CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText
    
    If Err.Number = 0 Then
        
        If Not rst.EOF Then

'--- do stuff        

        End If
        rst.Close
    End If
    
    
'---- errors?
    If Err.Number = 0 Then
        msgbox "Worked!"
    Else
        MsgBox "Error : " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbExclamation, "Error"
        Err.Clear
        
    End If
    
    
'---- clear up
    
    Set rst = Nothing
    
End Sub

EDIT:
And I try it this morning and it works...
Stupid MS Access grrrr If anyone still has come across it and knows what the problem was, please let me know so I can avoid it next time.

RE-EDIT:
Ok the simplistic works, but as soon as I put in the complicated it returns no records
<insert curses here> ms access
Here's the code:

    If chkFilter Then
        If lstUsers.ItemsSelected.Count > 0 Then
            For lngLoop = 0 To lstUsers.ItemsSelected.Count - 1
                strWhere = strWhere & IIf(Len(strWhere) > 0, ",", "") & lstUsers.Column(0, lstUsers.ItemsSelected(lngLoop))
            Next
        End If
    End If
       
    
    strSql = "SELECT tblLCasesLiveTasks.AssignedToID, tblLCasesLiveTasks.ActionByDate, tblLLUStatusTasks.StatusTask, tblLLUTasks.Task, tblLCasesLiveTasks.CaseID AS CaseNo, tblLCasesPersonalDetails.Forename, tblLCasesPersonalDetails.Surname, nz(tblLUsers.Forename,'Not Assigned') AS AssToFN, nz(tblLUsers.Surname,'') AS AssToSN" & vbCrLf
    strSql = strSql & " FROM (((tblLCasesLiveTasks LEFT JOIN tblLLUTasks ON tblLCasesLiveTasks.TaskID = tblLLUTasks.TaskID) LEFT JOIN tblLCasesPersonalDetails ON tblLCasesLiveTasks.CaseID = tblLCasesPersonalDetails.CaseID) LEFT JOIN tblLLUStatusTasks ON tblLCasesLiveTasks.TaskStatusID = tblLLUStatusTasks.StatusTaskID) LEFT JOIN tblLUsers ON tblLCasesLiveTasks.AssignedToID = tblLUsers.UserID" & vbCrLf
    strSql = strSql & " WHERE tblLCasesLiveTasks.ActionByDate <= #" & lblTwoWDs.Caption & "#"
    strSql = strSql & " And tblLLUStatusTasks.StatusTask Like 'To Be*'"
    If Len(strWhere) > 0 Then strSql = strSql & vbCrLf & " AND tblLCasesLiveTasks.AssignedToID in (" & IIf(chkIncUna, "0,", "") & strWhere & ")"
    
    Select Case fraOrderBy
        Case 1
            strSql = strSql & vbCrLf & " ORDER BY tblLCasesLiveTasks.CaseID,tblLCasesLiveTasks.ActionByDate"
        Case 2
            strSql = strSql & vbCrLf & " ORDER BY tblLUsers.Surname, tblLCasesLiveTasks.AssignedToID, tblLCasesLiveTasks.CaseID,tblLCasesLiveTasks.ActionByDate"
    End Select

Again, the sql returns no recs, but if I copy the sql string, it returns records... Is it something stoopid I'm missing here?