sql with recordset

abenitez77

Registered User.
Local time
Yesterday, 19:41
Joined
Apr 29, 2010
Messages
141
I get an error msg below. I am trying to run a SQL statement and create a recordset. The first recordset is created with no problems...but the second one is a problem.
This is the line that has the eror msg: "Too few parameters. Expected 1."
Set rs_Null = dbs.OpenRecordset(strSQL)
'Full Code:
Dim lngColumn As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rs_Null As DAO.Recordset
Dim myField As String
Dim ReqFld As String
Dim strSQL As String

Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("tbl_Claims_Upload", dbOpenDynaset)
n = rst.RecordCount
If rst.RecordCount <> 0 Then
' write data to the recordset
'Do While Not rst.EOF
'rst.Fields(lngColumn).value
For i = 0 To n - 1
Set td = dbs.TableDefs(i)
For Each fld In td.Fields
myField = fld.Name
myType = FieldType(fld.Type)
strSQL = "Select * From tbl_Claims_Upload Where " & myField & " Is NULL" ' OR LEN(Rtrim(Ltrim(" & myField & "))) < 1"
Set rs_Null = dbs.OpenRecordset(strSQL)
n2 = rs_Null.RecordCount
If n2 = 0 Then
'If DCount("*", "tbl_Claims_Upload", myField & " Is Null") = 0 Then 'Or DCount("*", "tbl_Claims_Upload", IsEmpty(myField)) = -1 Then
ReqFld = ReqFld + myField & " has empty Values" & Chr(13)
Else
End If


Next fld

Next i
MsgBox (ReqFld)
'Loop
End If
'rs.Fields("somefield").Type
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
I should state...i remmed out the line below in part of the sql code (right before the OR) on purpose to simplify it...but it gives me the same error msg:

strSQL = "Select * From tbl_Claims_Upload Where " & myField & " Is NULL" ' OR LEN(Rtrim(Ltrim(" & myField & "))) < 1"
 
Try without the quotes in red:

strSQL = "Select * From tbl_Claims_Upload Where " & myField & " Is NULL" ' OR LEN(Rtrim(Ltrim(" & myField & "))) < 1"
 
Yeah that is what I though initially, but the OP has simply commented out the OR statement.
 
Duh; I should have noticed that. Add this line right before opening the recordset:

Debug.Print strSQL

which will print the finished SQL out to the VBA Immediate window. If you don't spot the problem, post it here.
 
I get the same error...

Runtime error '3061':
Too few parameters. Expected 1.
 
"Too few parameters. Expected 1."


Usually means that the name of the field you have passed to the statement does not exist in the source table/query. Like stated do a Debug.Print on the SQL to test for field names and spellings/spaces in fieldnames is usually a big hint.

Fields with spaces in their names need [] around them.
 
I get the same error...

As noted, the technique I gave you wasn't intended to fix anything. It's a debugging tool to help you find where the problem is.
 
I figured out what was wrong with my original code. I was using a field name in the sql command that did not exists in the table.

I ended up using this code:
Public Sub fFindNulls(strTableName As String)
Dim rstAny As Recordset
Dim iFldCount As Long
Dim iLoop As Long
Dim strFldName As String
Dim strSQL As String
Dim dbAny As DAO.Database
Dim iRecCount As Long
Dim strMsg As String
Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strTableName)
iFldCount = rstAny.Fields.Count

For iLoop = 0 To iFldCount - 1
strFldName = rstAny.Fields(iLoop).Name
strSQL = "SELECT COUNT(*) FROM [" & strTableName & _
"] WHERE Trim([" & strFldName & "] & '') = ''"
iRecCount = dbAny.OpenRecordset(strSQL).Fields(0)
If iRecCount > 0 Then
strMsg = strMsg & iRecCount & " null values in field " & strFldName & vbCrLf
End If
Next iLoop
MsgBox strMsg
End Sub
 

Users who are viewing this thread

Back
Top Bottom