Automatically Change 'Null' to '0' to unknown # of fields

[DustDevil1980]

I have a table that contains a number of nulls. It is created from a number of select queries and a make table query and New fields are frequently added to it. I need a way to scan this table to add a '0' for each field that is 'Null'. The only solution I have been able to come up with is:

iif([field1] Is Null, 0,)

the problem with this is, someone would have to update the query every time a new field was added.

 

[KenReay]

Hi

Would the Nz() function help ?, see help for syntax and action

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Edski]

Hi DustDevil1980,
To loop through the fields collection of a tabledef (without knowing how many fields there are) just use the
For...Next loop structure.

Dim tdf as TableDef, fld As Field
For each fld in tdf.Fields
    If IsNull(fld.Value) Then fld.Value = 0
Next

Does that Help?

 

[dhookom]

Edski,
I believe the fld.Value will either fail or reference the name property. The code would need to loop and execute sql statements:

Sub FixNulls()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim fd As DAO.Field
    Dim strSQL As String
    Set db = CurrentDb
    For Each td In db.TableDefs
        'don't mess with system tables
        If Left(td.Name, 4) <> "msys" And td.Connect = "" Then
            For Each fd In td.Fields
                'ignore autonumber fields
                If fd.Attributes <> 17 Then
                    Select Case fd.Type
                        Case dbDecimal, dbInteger, dbLong, dbByte, _
                            dbBigInt, dbBoolean, dbCurrency, dbDouble, _
                            dbFloat, dbNumeric, dbSingle
                            strSQL = "Update [" & td.Name & "] SET [" & _
                                fd.Name & "] = 0 WHERE [" & fd.Name & _
                                "] is Null"
                            Debug.Print strSQL
    
                            db.Execute strSQL, dbFailOnError
                        Case Else
                            'ignore other fields
                    End Select
                End If
            Next
        End If
    Next
    Set fd = Nothing
    Set td = Nothing
    Set db = Nothing
End Sub

Duane
MS Access MVP
Find out how to get great answers faq219-2884.