ADO recordset null record displayed as 1

[Jon von der Heyden]

Hi

Another quirk I can't see reference to.

I am pulling a small recordset out of an Access database. It is a single field recordset. Some records are null. If I try and load the recordset into an array I get a mismatch error. The field is set as a text fields. On closer inspection I notice that the null value is converted into the number 1 (hence the mismatch).

I found out using:

?m_clsDB.RunScript(strSQL).GetString(adClipString,-1,"|",vbcrlf,vbnull)

(The RunScript function returns a recordset using the recordset open method.)

Which returned:

[B][COLOR="Red"]1[/COLOR][/B]
Development & Projects
IT
Local Buying
Logistics
Mobility
Professional Services

The #1 above is actually a null value in the recordset. Why is it being converted to #1?

 

[RoryA]

Because that's what vbNull is...

 

[Jon von der Heyden]

Oooohh I see! Ok so I change it to vbNullString and it's as expected.

So the mismatch when converting the recordset to variant array isn't because it contains a number record. Any idea why else I'd get a mismatch error when using GetRows? Is it simply because it doesn't like a null item?

 

[RoryA]

What variable type are you assigning to? GetRows returns a variant.

 

[Jon von der Heyden]

Dim varData as Variant

And then:

varData = Application.Transpose(m_clsDB.RunScript(strSQL).GetRows)

 

[RoryA]

What's the full code line?

 

[Jon von der Heyden]

I was editing when you replied. Actually the complete code is:

Public Property Get Categories() As Variant
    Dim strSQL As String
    
    strSQL = "SELECT DISTINCT KPI_CATEGORY FROM MKPI_TBL;"
    Categories = Application.Transpose(m_clsDB.RunScript(strSQL).GetRows)
End Property

 

[RoryA]

I suspect it's an issue with Transpose. If you remove that does it work?

 

[Jon von der Heyden]

Yes it does work. Although I'm sure I had a good reason for using Transpose, only I can remember now.

Thanks...again!

 

[RoryA]

GetRows returns the columns as the first dimension, which is why you need to transpose. My preference is to use my own transpose function rather than Application.transpose which seems to be touchy about a lot of things.