Null field in Access goes into Excel as zero-value. Array of user-defined type as intermediary.

[Colleyville Alan]

I am writing an app that will query mutual fund performance data from an
Access table, load it into memory and then write formatted info to Excel. I
have created a user-defined type of several different variables, then, upon
completion of the query, I declare an array of that type whose size matches
the number of records in the results table. I then loop through the results
table and assign each value to my array.

I check each element to see if it is null before assigning it to the array,
lest I get a type mismatch error. For example, to load the returns of a
fund for the last ten years into the array, I use the following:

If ![10-Years] <> "" Then
FundPerfArray(iCtr).dbl3Years = ![10-Years]
End If

Where "FundPerfArray" is an array of the user-defined type.

The problem is, when it encounters a null value, the array holds a zero in
its place. When I get to Excel, it writes a zero in the cell representing
10 years. Now I could change zeroes to blanks, but then a fund could
actually have exactly zero for that time, unlikely as it sounds. A
complicated workaround would be to double the fields in the custom type and
have one to hold nulls for each period and then with some IF statements
figure out which to load and which to write. But that is horrible
convoluted. Is there a simple way to eliminate the zeroes in array elements
that have not been assigned because the underlying data is null?

Thanks

 

[Bill James]

The problem is VB initializes declared variables for
you...

Can you explicitly initialize your array elements to a
known value before you fill it (i.e. null)?

 

[Colleyville Alan]

The problem is VB initializes declared variables for
you...

Can you explicitly initialize your array elements to a
known value before you fill it (i.e. null)?

I will give that a try. Thanks.

-----Original Message-----
I am writing an app that will query mutual fund performance data from an
Access table, load it into memory and then write formatted info to Excel. I
have created a user-defined type of several different variables, then, upon
completion of the query, I declare an array of that type whose size matches
the number of records in the results table. I then loop through the results
table and assign each value to my array.

I check each element to see if it is null before assigning it to the array,
lest I get a type mismatch error. For example, to load the returns of a
fund for the last ten years into the array, I use the following:

If ![10-Years] <> "" Then
FundPerfArray(iCtr).dbl3Years = ![10-Years]
End If

Where "FundPerfArray" is an array of the user-defined type.

The problem is, when it encounters a null value, the array holds a zero in
its place. When I get to Excel, it writes a zero in the cell representing
10 years. Now I could change zeroes to blanks, but then a fund could
actually have exactly zero for that time, unlikely as it sounds. A
complicated workaround would be to double the fields in the custom type and
have one to hold nulls for each period and then with some IF statements
figure out which to load and which to write. But that is horrible
convoluted. Is there a simple way to eliminate the zeroes in array elements
that have not been assigned because the underlying data is null?

Thanks


.

 

[Colleyville Alan]

The problem is VB initializes declared variables for
you...

Can you explicitly initialize your array elements to a
known value before you fill it (i.e. null)?

It just occurred to me that in the custom type, the elements are defined by
the type of data they will hold (string, integer, double). Is it possible
to assign a null to an integer or double field? Do I have to use variant
types?