Results 1 to 2 of 2
2004-06-04, 16:29 #1
- Join Date
- Jun 2001
- Virginia, USA
- Thanked 0 Times in 0 Posts
field names as variables in SQL statement (XP)
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"
2004-06-04, 16:40 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: field names as variables in SQL statement (XP)
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"
- 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