Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    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"
    DoCmd.RunSQL strSQL
    Next fld

    End Sub


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts