"Invalid use of Null" when using Str() with a Null Recordset field, but Str(Null) works fine

Question

I'm banging my head against the wall on this one. I was looking at some old database reporting code written in VB6 and came across this line (the code is moving data from a "source" database into a reporting database):

rsTarget!VehYear = Trim(Str(rsSource!VehYear))

When rsSource!VehYear is Null, the above line generates an "Invalid use of Null" run-time error. If I break on the above line and type the following in the Immediate pane:

?rsSource!VehYear

It outputs Null. Fine, that makes sense. Next, I try to reproduce the error:

?Str(rsSource!VehYear)

I get an "Invalid use of Null" error.

However, if I type the following into the Immediate window:

?Str(Null)

I don't get an error. It simply outputs Null.

If I repeat the same experiment with Trim() instead of Str(), everything works fine. ?Trim(rsSource!VehYear) returns Null, as does ?Trim(Null). No run-time errors.

So, my question is, how can Str(rsSource!VehYear) possibly throw an "Invalid use of Null" error when Str(Null) does not, when I know that rsSource!VehYear is equal to Null?

---

Update: If I type the following in the Immediate window, it works as expected (no error occurs):

?Str(rsSource!VehYear.Value)

This outputs Null. Now, I know that rsSource!VehYear is actually an ADODB.Field instance, but Value is its default property, so Str should be operating on the Value property (which is Null). Even the error message ("Invalid use of Null") suggests that Str is receiving a Null parameter, but how can it treat Null differently in one case and not the other?

My only guess is the internal implementation of Str() is somehow failing to get the default property, and the "Invalid use of Null" error is happening for a different reason (something other than the parameter is causing the "Invalid use of Null", perhaps when it is trying to retrieve the default property from the Field object).

Does anyone have a more detailed, technical explanation for what is actually happening here?

In short:

?Str(rsSource!VehYear)

throws an "Invalid use of Null" error when rsSource!VehYear is Null, but

?Str(rsSource!VehYear.Value)

returns Null.

However, both Trim(rsSource!VehYear) and Trim(rsSource!VehYear.Value) return Null.

Answer 1

If you need a value other than a string, try using IsNull instead:

rsTarget!VehYear = IIf(IsNull(rsSource!VehYear), 0, rsSource!VehYear)

' Note 0 is the default value

Answer 2

The Str function will specifically check if a Null value is passed in and deal with it accordingly. When you pass in an object it attempts to convert the result of a default method to a String. The result of the default method is not passed into the Str method, but Field object is, so a check for the initial Null will fail. The Str function will continue to check the parameter type for datatypes that it supports when it realizes that it has an object, it will attempt to retrieve the default value. It doesn't re-attempt to deal with the default value as it did with the passed in argument, so the attempt to return a Null as a String will fail. It seems MS didn't expect a default value to be Null or any other invalid value for Str. For instance Str doesn't support an empty string either.