replacing an empty string with a null value

[michee]

I have a module that creates records into a contacts table in my access database. When there is no data in the field from outlook, the data is populated as an empty string "" instead of a null value, creating problems with subsequent queries and processes. I'd like to create the data as a null value but the module keeps failing with "object is required" on the statement

rstImport("Customer ID").Value = System.DBNull.Value

it doesn't seem to like System.DBNull.Value or DBNull.Value, and when I assign the vbNullString constant instead, it gets set to "" again and not null.

help?

 

[MarkK]

In VBA you can just use 'Null' so . . .

Field.Value = Null

 

[michee]

Ok - that was ridiculously easy... and worked, thank you. I think what got me into this loop is I started out writing a function to replace empty string with a defined replacement value similar to the nz function. When I passed Null as an argument I got an "invalid use of null" error. So how should I be passing null?

rstImport("Profession").Value = NzEmpty(.Profession, Null)

Public Function NzEmpty(AnyString As String, ReplaceString As String)

If (AnyString = "" Or IsEmpty(AnyString)) Then
NzEmpty = ReplaceString
Else
NzEmpty = AnyString
End If
End Function

 

[vbaInet]

String cannot be Null, Variant can.

IsEmpty() function is not what you think it is. Use one of:

If Len(Anystring & vbN) = 0 Then

If Nz(AnyString, vbNullString) = vbNullString Then

 

[MarkK]

To elaborate in vbaInet's post a little . . .

Empty and Null are two special values that can be stored by a Variant datatype. Here's a little code block that explores that a little . . .

Sub TestVariant()
   dim v as variant
   debug.print "On Init", IsEmpty(v), IsNull(v)
   v = null
   debug.print "To Null", IsEmpty(v), IsNull(v)
   v = empty
   debug.print "To Empty", IsEmpty(v), IsNull(v)
End Sub

Null is also the value of a field in a table after the row has been created, but before a value has been assigned to the field.

'Empty' has the numerical value of zero, and you can do math with it. 'Null' propagates through expressions, so if you do math with a Null, you get Null, so that Nz() function is really handy. It's the only thing, along with the '&' string concatenation operator, that doesn't choke on a Null.

A "null string" by contrast, is simply a string with no characters in it, like . . .

dim s1 as string
dim s2 as string

[COLOR="Green"]'these are both null strings[/COLOR]
s1 = ""
s2 = vbNullString

[COLOR="Green"]'ergo this expression is true[/COLOR]
debug.print s1 = s2

I might refactor your function like this . . .

Public Function Sz(AnyString As String, ReplaceString As String) As String
    Sz = AnyString
    If AnyString = "" Then Sz = ReplaceString
End Function

. . . so in the first line we just do the most likely thing, and in the second line we do the test, and make changes if the test fails.

Hope this helps,

 

[michee]

success! After a couple of variations I figured out the right solution. I'm posting the final answer here ... of course if you have any simplifying suggestions that would be dandy.

If you want to import data into an access table, and you want data not populated in the source to come through as NULL in the target (so that "is null" in queries works) you have to use the variant data type. As you so clearly explained, strings aren't null. Thanks you SO much for your help.

So my final solution looks like this:

'function call to convert empty string to NULL

rstImport("Profession").Value = NzEmpty(.Profession, Null)

'public function NzEmpty

Public Function NzEmpty(AnyString As String, ReplaceString As Variant) As Variant
NzEmpty = AnyString
If AnyString = "" Then NzEmpty = ReplaceString
End Function