Replace ' ' with NULL - whole table 1

[micang]

Access 2003

Hi All,

I have a table in Access with 70 or so fields.

Is there a way to replace any record that has a blank (' ') with a NULL across the whole table in one go as opposed to running a separate query for each field?

Any suggestions appreciated.


Thank you

Michael

 

[genomon]

You can loop through the fields collection of the table object, using UPDATE SQL and DoCmd; abundant documentation in Access help...

Silence is golden.
Duct tape is silver.

 

[micang]

Thank you genomon, appreciated.

Michael

 

[genomon]

OK finally found similar code I use to reset field attributes - you can modify it to update (goes thru ALL tables though!):

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field

Set db = CurrentDb

For Each tbl In db.TableDefs
  With tbl
  'Debug.Print tbl.Name
    If .Attributes = 0 Then 'Non-system tables only 
        For Each fld In .Fields
            With fld
               If .Type = dbText Or .Type = dbMemo Then
                 
               'Obviously only looking at text & memo
               'but you can change that. 
               'Could add update code here

               End If
            End With
        Next fld
      End If
   End With
Next tbl
Set db = Nothing
End Sub

Hope this helps a little better!!

Silence is golden.
Duct tape is silver.

 

[micang]

Another star for you - this is brillaint, thank you very much.

Michael