VBA convert dao.recordset null values to zeroes

[baitmaster]

Hi all, is there a simple way to do this please?

I have extracted a recordset from access, and am holding it in excel VBA, and performing various actions on it. I keep hitting problems with null values, and want them to be seen as zeroes instead

Can I change my SQL query to SELECT(*) from Database() etc, to include a convert null to 0 action? Or something similar?

thanks

 

[RoryA]

Yes you can, using an IIf(IsNull(field),0,field) type of expression.

 

[baitmaster]

thanks yet again Rory, this will help

Do you know if this sort of approach can be written as a default into the SQL?

I assume not, and will use ISNULL to update my entire recordset in one go, prior to passing around my various macros. It would be much simpler to have it defined like that in the first place though...

cheers

 

[RoryA]

No, there's no default option that I know of. Normally the database might be setup to default to 0 and not allow null values (I don't think the Jet provider understands Nz which would be a little shorter to type!)

 

[baitmaster]

Thanks again Rory

my first attempt at a "null converter" shows that my guesses about object types / parameters is incorrect, but hopefully you can see what I attempted to do - the whole DAO thing is still new to me. Is something like this possible do you think, or do I have to riddle my entire set of macros with individual error handlers?

        With rsRecordSet
 
            [COLOR=seagreen]' convert recordset null values to zeroes[/COLOR]
            .MoveFirst
            Dim objfield ' [COLOR=red]not sure what to dim as[/COLOR]
            Do While Not .EOF
                For Each objfield In .Fields [COLOR=red]' actually I think I want "columns", but dont know the name, if there is one[/COLOR]
                    If IsNull(.Fields(objfield.Index)) Then .Fields(objfield.Index) = 0 [COLOR=red]' clearly this is wrong, but might show you the construct Im looking for. I'm not even sure if index is right :([/COLOR]
                Next objfield
                .MoveNext
            Loop
 
            [COLOR=seagreen]' go back to start of recordset[/COLOR]
            .MoveFirst

 

[RoryA]

It would be much better to do it in the SQL if you can. If you must do it in the recrodset then:

If IsNull(objfield.Value) Then objfield.Value = 0

 

[baitmaster]

hmm ok... and this updates all the separate pieces of information in each field / record at the same time does it? I thought I would have to loop through each column, this suggests I dont. I'll try it now...

thanks

 

[RoryA]

You are looping through each column, as well as through each record.

 

[baitmaster]

riiiigggght...! Now I sort of get it, thanks

ok, for anyone else reading later, this is where I got to. It works, but isn't necessarily the optimum way to do it. Laugh at my attempt if you want

        With rsRecordSet
            
            ' convert recordset null values to zeroes
            .MoveFirst
            Dim objfield
            Do While Not .EOF
                For Each objfield In .Fields
                    If IsNull(objfield.Value) Then
                        .Edit
                        objfield.Value = 0
                        .Update
                    End If
                Next objfield
                .MoveNext
            Loop
            
            .MoveFirst
            
            ' further actions, end with, etc...

 

[Ruddles]

using an IIf(IsNull(field),0,field) type of expression.

I think Rorya may have been suggesting using this in your SQL so that null fields are actually output as zeroes - so that Excel never sees the nulls and you don't have to code round them.

So instead of:-

SELECT [table].[field]

you'd have:-

SELECT If(IsNull([table].[field]),0,[table].[field]) AS fieldname

PS. Did you do Wootton Bassett on Sunday?