Attribute VB_Name = "basRidOfNull"
Option Compare Database
Option Explicit

Sub RidOfNulls()

'-------------------------------------------------------------
' Purpose : This Sub Procedure will go through all text
'           fields in a table and replace NULLS with "-"

' Notes : Uses ADO 2.5
'           You will enter the table name and your
'           replacement text in Input Boxes
'-------------------------------------------------------------

'Declare variables
Dim cnConn As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim fldTitle As ADODB.Field
Dim X As Integer
Dim strTableName As String
Dim strReplaceWith As String
Dim TimeStart, TimeEnd, Result
Dim HowManyNulls As Long

'Set the total Nulls to 0
HowManyNulls = 0

'Set Connection and Recordset
Set cnConn = CurrentProject.Connection
Set rsTable = New ADODB.Recordset

'In case of error exit sub
On Error GoTo ErrorHandler:

'Input the table name
strTableName = InputBox("Enter the name of the table to replace NULLS:  ", "Input Table Name")

'Input your replacement string
strReplaceWith = InputBox("Enter the string to replace NULLS:  ", "Replace With...")

'Open the recordset
rsTable.Open strTableName, cnConn, adOpenDynamic, adLockOptimistic
rsTable.MoveFirst

'Show the hourglass to amuse users
DoCmd.Hourglass True

'Time the editing process
TimeStart = Timer()

'------------Begin Editing----------------
Do Until rsTable.EOF
    'Use a FOR/NEXT to enumerate through the fields collection
    X = -1
    For Each fldTitle In rsTable.Fields
        X = X + 1
        'Debug.Print fldTitle.Name & " >>> " & rsTable(X)
        If IsNull(rsTable(X)) Then
            On Error Resume Next 'Will only update text fields
            rsTable(X) = strReplaceWith
            HowManyNulls = HowManyNulls + 1
            Debug.Print rsTable!NUM
            rsTable.Update
        End If
    Next fldTitle
    
    rsTable.MoveNext
Loop
'--------------End of Editing---------------

'End of timing
TimeEnd = Timer()

'The result of timing
Result = TimeEnd - TimeStart

'Good-bye hourglass
DoCmd.Hourglass False

'Clean up your variables
rsTable.Close
Set rsTable = Nothing
cnConn.Close
Set cnConn = Nothing

'Indicate successfull completion of operation
MsgBox "The operation has completed" & vbNewLine & _
        "in " & Result & " seconds." & vbNewLine & vbNewLine & _
        HowManyNulls & " NULLS were replaced with:   " & strReplaceWith, vbInformation, _
        "Finished Table - " & strTableName

'Exit procedure if all went well
Exit Sub

'Error Handler
ErrorHandler:
    MsgBox "An error occured, please try again", vbCritical


End Sub