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

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?

Code:

---

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 :mad: 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:

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?

Managed to debug it down to :

Code:

---

strSql = strSql & " And (tblLLUStatusTasks.StatusTask Like 'To Be*')"

---

Which for some reason seems to be filtering off all the records.


Wait.... Wait a minutes. Yes got it.

Crap

ADODB wants the % not * even for access. Right sorted. Thanks for your patience. :blush: :eek2: :bigyello: :D

thats goood...but before you post your question here... you have to try all the possibilities...

Yeah, but for some reason almost as soon as I post I find the resolution.
I spent an hour on it this morning and at least 1 hour yesterday trying to find out why it wasn't working right.