Navigation for dotNetTips.com - Dealing with Null Database Fields

The best way to deal with this problem is to use the built-in & operator to concatenate a blank string to each field as you read it. Concatenate 0 for numeric fields.

Sample Code

Dim dbBiblio As Database
Dim rsData As Recordset
Dim sYear As String
Dim sHireDate As Date
Dim lReports As Long
  Set dbBiblio = OpenDatabase("Northwind.mdb")
  Set rsData = dbBiblio.OpenRecordset("Employees")
  'Concatenate empty string ("") here with null values
  sYear = rsData![Title] & vbNullString
  'Concatenate zero so it does not error
  sHireDate = rsData![HireDate] & 0
  lReports = rsData![ReportsTo] & 0