Error 2147217904 No value given for one or more required parameters when querying Access from Excel

Question

I'm querying Table1 in Access from Excel. I'm filtering column SampleType in where clause based on three criteria checked on the userform.

I also need to return the number of distinct records from column SampleID based on selection in the where clause.

There is probably some minor problem with my code that I can't figure out. Please help. Thank you

Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQLwhere As String
Dim StrSql As String
Dim i As Integer
Dim tar6 As String
Dim tar7 As String
Dim tar8 As String
tar6 = "col"
tar7 = "lun"
tar8 = "mel"

'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False

dbPath = "C:\Users\Temp1\Documents\Annie\MDL_IonTorrent.accdb"
'set the search variable

Set cnn = New ADODB.Connection ' Initialise the collection class variable

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

'Create the SQL statement to retrieve the data from table.

SQLwhere = "WHERE "

If CheckBox2 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & tar6 & "%" & "' AND "
'count a total district records in column SampleID.
End If

If CheckBox3 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & tar7 & "%" & "' AND "
End If

If CheckBox4 = True Then
SQLwhere = SQLwhere & "[Table1].[SampleType] LIKE '" & tar8 & "%" & "' AND "
End If

StrSql = "SELECT COUNT(*) AS UniqueRecordsCount FROM (SELECT DISTINCT [SampleID] FROM [Table1]) "


 'Remove the last AND applicable

If SQLwhere = "WHERE " Then
    SQLwhere = ""
Else
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 5)
End If

StrSql = StrSql & SQLwhere


'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

'ConnectionString Open '--5 aguments--
'Source, ActiveConnection, CursorType, LockType, Options
rs.Open StrSql, cnn


GetDistinctValue = rs("UniqueRecordsCount")
MsgBox (GetDistinctValue)

UserForm1.Controls("txtCrt6").Value = rs!UniqueRecordsCount

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"

Exit Sub
End If

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing

'Enable the screen.
Application.ScreenUpdating = True


'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FindSampleId"

Answer 1

I encountered this same error and found out after many hours that I had copied a SQL statement from a Word document and Word had replaced standard quotes (') with specially formatted slanted quotes. I only discovered the difference after copying the SQL statement to notepad and retrying it.

Answer 2

The parenthesis is in the wrong place. For example, if just CheckBox2 was true then your SQL statement would end up like:

"SELECT COUNT(*) AS UniqueRecordsCount FROM (SELECT DISTINCT [SampleID] FROM [Table1]) WHERE [Table1].[SampleType] LIKE '" & tar6 & "%" & "'"

The parenthesis after FROM [Table1]) needs to move to the very end of the SQL because otherwise you are checking the value of [SampleType] in a table which only has one column - [SampleID]

So:

StrSql = "SELECT COUNT(*) AS UniqueRecordsCount FROM (SELECT DISTINCT [SampleID] FROM [Table1] "

then later:

If SQLwhere = "WHERE " Then
    SQLwhere = ")"
Else
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 5) & ")"
End If