sql with recordset

[abenitez77]

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"
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

 

[dhookom]

If you have mistakenly allow any fields to have spaces in the names, you will need to use:

 strSQL = "Select * From tbl_Claims_Upload Where [" & myField & "] Is NULL"

Adding the []s will work with spaces or no spaces. Other characters can also cause issues.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

I put a watch on strSQL, this is what the result is:

"Select * From tbl_Claims_Upload Where ClaimsID Is NULL OR LEN(Rtrim(Ltrim(ClaimsID))) < 1"

ClaimsID is the name of the field.

 

[dhookom]

Can I ask the purpose of the code and why you need two recordsets from the same table?

The .RecordCount property isn't always reliable without a .MoveLast.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

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