Null Handling in Database I/O

Null Handling in Database I/O

Nulls are still a problem in VB3/VB4-they cause unexpected errors. To handle them, you can read the MDB field into a variant, which can hold a null and then test for it later, or replace all nulls with blank strings to protect the application. To me, nulls serve no logical purpose. I prefer to eradicate them immediately. I have developed a family of I/O pick-up routines that I use at the physical interface next to the database field:

 Public MyDB As DatabasePublic MyRS As RecordsetDim TestVariant As VariantSet MyDB = OpenDatabase("testjet3db")Set MyRS = MyDB.OpenRecordset("NameTable")'Sample CallsTestVariant = ScreenForNull(MyRS![FirstName])TestVariant = ScreenForNull(MyRS![FirstName], " ")Public Function ScreenForNull(aField As Variant, Optional _	ByVal DefaultReturn As Variant) As Variant	If IsNull(aField) Then		If Not _			IsMissing(DefaultReturn) Then			ScreenForNull = DefaultReturn		Else			ScreenForNull = ""		End If	Else		ScreenForNull = aField	End IfEnd Function


Share the Post: