Trouble with SQL Query SELECT Count...

[ChrisBrook]

Hi,

I've built a report in MS Excel which contains multiple tabs and extracts information from a MS Access database.

As part of this I'm trying to select a specific table and count the number of rows where a certain criteria is met before then moving on to do the same on another table.

In my spreadsheet I have a tab called 'Completed', on here I have a list of the table names that I'm refrencing in the query.

The code I'm currently using is:

Public Sub ImportCompleted(StartDate As Date, EndDate As Date)
Dim strTableName As String, sSQL As String, lngRow As Long

'This calls a seperate sub which contains the DB path & password etc.
OpenDataBase MIDataBasePath, MIDataBasePassword


sSQL = "SELECT * FROM tbl_Reports ORDER BY QueueType, Description"


RetrieveDataBaseData (sSQL)
If RecordSet.EOF Or RecordSet.BOF Then
    MsgBox ("No Records Found")
    End
End If


'Generates Header on Worksheet
With Sheets("Completed").Cells(1, 1)
    .Value = "QueueView Daily Figures Report for " & VBA.Format(StartDate, "dd/mm/yyyy")
    .Font.Bold = True
    .Font.Italic = True
    .Font.Size = 24
End With


'Generate Column Headers
Sheets("Completed").Cells(2, 2).Value = "Code"
Sheets("Completed").Cells(2, 3).Value = "Description"
Sheets("Completed").Cells(2, 4).Value = "Completed (-1 Day)"


'Add Queue Names
lngRow = 3
Do Until RecordSet.EOF
    strTableName = RecordSet.Fields("TableName").Value
    strQueueType = RecordSet.Fields("QueueType").Value
    Sheets("Completed").Cells(lngRow, 1).Value = RecordSet.Fields("TableName").Value
    Sheets("Completed").Cells(lngRow, 2).Value = RecordSet.Fields("Code").Value
    Sheets("Completed").Cells(lngRow, 3).Value = RecordSet.Fields("Description").Value


    RecordSet.MoveNext
    lngRow = lngRow + 1
Loop


'Add Completed Values
lngRow = 3


For i = 3 To Sheets("Completed").Range("A1").End(xlDown).Row
    strTableName = Sheets("Completed").Cells(lngRow, 1).Value
    
    sSQL = "SELECT Count(*) FROM (" & strTableName & ") WHERE (((" & strTableName & ".Completed_TimeStamp) >= #" & VBA.Format(StartDate, "mm/dd/yyyy") & "#) And (" & strTableName & ".Completed_TimeStamp) < #" & VBA.Format(EndDate + 1, "mm/dd/yyyy") & "#)"
    
    Sheets("Completed").Cells(2, 5).Value = RecordSet.Fields("CountOfRecord_ID").Value
    
Next i


RecordSet.Close
DataBaseConnection.Close


End Sub

The last sSQL presents me with the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."

I've checked and double checked and each table is formatted the same with the following columns:
Record_ID
Checkout_User
Completed_User
Completed_TimeStamp
Instance_ID

Any ideas?

Thanks
Chris

 

[RoryA]

You don't appear to actually do anything with that SQL string that I can see.

 

[ChrisBrook]

What a fool! Yeah I missed out the following line:

RetrieveDataBaseData (sSQL)

After adding this in and using the string I get the same error though...

 

[RoryA]

Do any of your table names have spaces in? What is the SQL that produces the first error?