Does a Query like the following one exist :

UPDATE FieldName FROM TableName WHERE FieldName IN('NA') SET FieldName = NULL

Fact is that FieldName can be any field ?

For Example

After Executing query I should have :

Please kindly note that I'm working on access.

CodePudding user response：

One thing that you could do is to use some VBA to loop the fields and run a set of update statements. Something like:

Sub sUpdateData()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Set db = CurrentDb
    Set tdf = db.TableDefs("TableName")
    For Each fld In tdf.Fields
        strSQL = "UPDATE TableName SET [" & fld.Name & "]=NULL WHERE [" & fld.Name & "]='NA';"
        db.Execute strSQL
    Next fld
    Set tdf = Nothing
    Set db = Nothing
End Sub

CodePudding user response：

No. Field and table names in SQL cannot be dynamic.

Use DAO for such tasks:

Public Function CleanNa()

    Dim Records As DAO.Recordset
    Dim Field   As DAO.Field
    
    Dim Sql     As String
    
    Sql = "Select * From Tablename"
    
    Set Records = CurrentDb.OpenRecordset(Sql)
    
    Records.MoveFirst
    While Not Records.EOF
        For Each Field In Records.Fields
            If Field.Value = "NA" Then
                Records.Edit
                Field.Value = Null
                Records.Update
            End If
        Next
        Records.MoveNext
    Wend
    Records.Close
    
End Function

CodePudding user response：

Have to explicitly reference each field to update.

Consider example with 2 fields:

UPDATE tablename SET Field2 = IIf(Field2 = "NA", Null, Field2),
                     Field3 = IIf(Field3 = "NA", Null, Field3)

Alternatively, build VBA procedure to perform edits on recordset or run repetitive UPDATE action statements in a loop.

Otherwise, open table and run a one-time Find/Replace on the entire table.