Dealing with Empty Cells in VBA for Access

Question

I have the following code which is supposed to step through an array of fields and create two new arrays to add to a new recordset:

For Each Field In SDSRecordsets(i)
    Debug.Print (j)
    Debug.Print (Field.Value)
    fieldNames(j) = Field.Name
    If Field.Value = Null Then
        values(j) = ""
    Else
        values(j) = Field.Value
    End If
    j = j + 1
Next

The first time this loop runs, the Debug.Print lines print out 0 and then the string value in the first cell as it should. It then runs through the rest of it with no problems. The second time, it tries to add an empty cell. The first Debug.Print prints out 1, as it should, and the second prints out Null, also doing as it should. However, I then get a compile error on the line:

values(j) = Field.Value

Can anyone explain why it is reaching this line, because the way I see it, the If statement must be evaluating Null = Null as false for this to happen.

I've also tried doing this with:

If Not IsEmpty(Field.Value) Then

But that doesn't work either.

Right after posting this I realized that it should be: If Not IsEmpty(Field) Then and that works properly. I'd still like to know why the other way doesn't work though. — Ulthran, Jul 08 '16 at 15:05
The condition, `Field.Value = Null`, can **never** be True because [Null is never equal to anything, not even Null.](http://stackoverflow.com/a/5663965/77335) — HansUp, Jul 08 '16 at 15:09
Is there a function or something for testing Null values then? I'm used to languages like Java and C where null does equal null. — Ulthran, Jul 08 '16 at 15:11

score 1 · Answer 1 · answered Jul 10 '16 at 07:40

Use the Nz function:

For Each Field In SDSRecordsets(i)
    Debug.Print (j)
    Debug.Print (Field.Value)
    fieldNames(j) = Field.Name
    values(j) = nz(Field.Value,"")
    j = j + 1
Next

Also you can use isnull([expr]) function, the direct comparison with null will not work

Dealing with Empty Cells in VBA for Access

1 Answers1