Empty text values aren't null or zero-length, but 2-byte null (\x0000)

[Mark Steward]

I've got a table with some apparently null text values, but VB shows that
they're actually a double zero byte. This makes it impossible to use
IsNull, IS NULL or Len to determine whether the value's empty.

Any ideas where these have come from before I programmatically replace them
with an empty string?

Cheers,

Mark

 

[Allen Browne]

It is very unlikely that this data was input through the interface.

It probably came either by import from other software, or else from a
function which performed an API or DLL call and mis-calculated the length of
the string - possibly adding 1 character where it should have subtracted the
null terminator.

Because Access was written in C, it can have difficulty in identifying the
null char. VBA is fine. You can try criteria of:
Like "*" & Chr(0) & "*"
and see if JET can sort it out.

Why you would replace them with a zero-length-string instead of Null beats
me though.

 

[Mark Steward]

It is very unlikely that this data was input through the interface.

It probably came either by import from other software, or else from a
function which performed an API or DLL call and mis-calculated the length
of the string - possibly adding 1 character where it should have
subtracted the null terminator.

Because Access was written in C, it can have difficulty in identifying the
null char. VBA is fine. You can try criteria of:
Like "*" & Chr(0) & "*"
and see if JET can sort it out.

Why you would replace them with a zero-length-string instead of Null beats
me though.

That was my guess, too. I only said zero-length string because I can run
Len on that, but actually Null in this case is the more correct value.

I'm going to just change them all back to Null, as I don't wanna fix my
queries for what seems to be a bug...

Thanks,

Mark