field names as variables in SQL statement (XP)

Topic

New Reply

I have to change zeros to nulls in a table. I think the SQL statement does not like the way I pass the field names to it. When I run the procedure, I get prompted for strTblField. Any suggestions would be appreciated.

Public Sub RemoveZeros()

Dim strSQL As String
Dim strTblField As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim tdfNew As DAO.TableDef

‘reference current db and the table to be cleaned up
Set dbs = CurrentDb
Set tdfNew = dbs.TableDefs(“tblMEFinalResults”)

‘Clean up the zeros from fields of the tblMEFinalResults
For Each fld In tdfNew.Fields
strTblField = “tblMEFinalResults.” & fld.Name
‘sql clean up sql statement
”””””””””””””’ I think the problem is here…
strSQL = “UPDATE tblMEFinalResults SET strTblField = Null WHERE strTblField = 0″
”””””””””””””
DoCmd.RunSQL strSQL
Next fld

End Sub

Thanks,

Reply | Quote

Replies

Because strTblfield is within the quotes, the code tries to update a field named strTblField (literally), and this does not exist, of course. Try this:

strTblField = fld.Name
strSQL = “UPDATE tblMEFinalResults SET [” & strTblField & “]= Null WHERE [” & strTblField & “] = 0”

Notes:
– It is not necessary to include the table name in strTblField.
-strTableField has been placed outside the quotes, so that the value of strTableField will be concatenated into strSQL instead of the name of the variable.
– I have put square brackets around it to avoid problems with spaces and other unusual characters in the field names

Reply | Quote

Because strTblfield is within the quotes, the code tries to update a field named strTblField (literally), and this does not exist, of course. Try this:

strTblField = fld.Name
strSQL = “UPDATE tblMEFinalResults SET [” & strTblField & “]= Null WHERE [” & strTblField & “] = 0”

Notes:
– It is not necessary to include the table name in strTblField.
-strTableField has been placed outside the quotes, so that the value of strTableField will be concatenated into strSQL instead of the name of the variable.
– I have put square brackets around it to avoid problems with spaces and other unusual characters in the field names

Reply | Quote