CODEWITHSUNDEEP
ms-access
this is DK
this is DK

Reputation: 39

Importing empty fields from Excel to Access

I am importing a table from Excel into Access

The import function works perfectly but empty fields are overriding the default 0 setting in Access.

The data from the table I am importing is being used in a calculated field somewhere else and cannot calculate using empty fields.

Question: How can I set multiple empty fields to 0 in Access after import?

Upvotes: 0

Views: 170

Answers (1)

Andre
Andre

Reputation: 27634

To set a single field, use a UPDATE query:

UPDATE myTable SET Column1 = 0 WHERE Column1 IS NULL

To set multiple fields, use multiple UPDATE queries in a loop, e.g.

Sub CleanUp()

    Dim arFields As Variant
    Dim vField As Variant
    Dim S As String

    ' The field names that can be NULL after import
    arFields = Array("Column1", "otherColumn", "[yet another one]")

    For Each vField In arFields
        S = "UPDATE myTable SET " & vField & " = 0 WHERE " & vField & " IS NULL"
        Debug.Print S
        CurrentDb.Execute S
    Next vField

End Sub

Upvotes: 1

Related Questions