Microsoft Excel data is rarely managed to prevent a user from leaving blanks (just spaces or a zero-length string) in worksheet cells. When the Excel worksheet is imported into a Microsoft Access table, the blanks come with it. Those blanks may cause problems later when doing searches or queries on the table. There are two VBA functions below that can be used to automatically change a blank to Null (no value at all).
BlankToNull() – This function removes leading or trailing spaces from a single value, and then passes back a Null if the only thing left is a zero-length string.
BlankToNullAllFields() – This function calls the BlankToNull() function for each text-related field in a table, changing blanks to Null for the whole table.
Here is the logic that I typically use to import and clean up Excel data:
- Use DoCmd.TransferSpreadsheet to import the Excel worksheet into a Microsoft Access table.
- Call the BlankToNullAllFields() function, using the table name for the function argument.
- Use a query to delete records from the table that only have Null in all the fields, or in the primary key field.
- If the table was created during the import, remove @ from the Format property of all fields in the table.
basBlankToNull
' basBlankToNull() Version 2.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
'
Public Function BlankToNull(ValueToCheck)
' This procedure returns a Null if ValueToCheck is made of blank strings.
' Otherwise returns a trimmed version of ValueToCheck.
On Error GoTo Err_Handler
Dim varValue
varValue = ValueToCheck
If IsNull(varValue) Then
BlankToNull = Null
Else
varValue = Trim(varValue)
If Len(varValue) = 0 Then
BlankToNull = Null
Else
BlankToNull = varValue
End If
End If
Exit_Proc:
On Error Resume Next
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "BlankToNull()"
BlankToNull = ValueToCheck
Resume Exit_Proc
End Function
'
Public Function BlankToNullAllFields(TableName As String) As Boolean
' This procedure trims all text fields in TableName, and changes blank
' strings to Null.
On Error GoTo Err_Handler
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim strName As String
Dim strSQL As String
Set db = CurrentDb
Set tbl = db.TableDefs(TableName)
' For each text-type field, update the field using BlankToNull().
For Each fld In tbl.Fields
Select Case fld.Type
Case dbChar, dbMemo, dbText
strName = fld.Name
strSQL = "UPDATE [" & TableName & "] " _
& "SET [" & TableName & "].[" & strName & "] = BlankToNull([" & strName & "]);"
CurrentDb.Execute strSQL, dbFailOnError
End Select
Next fld
BlankToNullAllFields = True
Exit_Proc:
On Error Resume Next
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbCritical, "BlankToNullAllFields()"
BlankToNullAllFields = False
Resume Exit_Proc
End Function
Making Microsoft Access Work. Call (505) 369-1650.