Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Newbie - how to change lots of fields

    I have an access 2003 database that has lots of records and lots of fields. Unfortunately the folks who created the database were even more clueless than I am and have used at least 5 different values for "missing data" which makes subsequent use of the data "less than easy".

    Is there an easy way to say

    replace any "no data", -99, -999, -9999, etc with null regardless of which
    field and which record it occurs in

    Thanks!
    Bruce

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    UPDATE <TableName> SET <ColumnName> = Null 
    WHERE <ColumnName> IN ('no data', -99, -999, -9999 ):
    If there are several columns (as in "many columns") looping in a recordset would probably be easier but slow:
    Code:
    Function SetColumnsToNull(ByVal TableName As String)
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(TableName, dbOpenDynaset)
        With rst
            Do Until .EOF
                .Edit
                For Each fld In rst.Fields
                    If Eval("'" & fld.Value & "' IN ( 'no data', '-99', '-999', '-9999' )") = True Then
                        fld.Value = Null
                    End If
                Next fld
                .Update
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Be sure that all columns accept Null values before trying anything and make a backup of the database first.
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    Worked like a champ. Thanks (and of course backup first :-)

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome
    Have a nice day!

Posting Permissions

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