When is an empty field in a table not really empty?

[psu_1995]

I have a DAO recordset query which I am running against a table that a user can change. The query looks at one particular column in the table and if a field is blank, it pulls data into that field from another table.

This all works fine except when a user deletes data from that column. The field looks empty to me when you view the table, but to the query, it's not. If I run a len() on the field, I get nothing - no 0, just blank. Data type for that column is set to txt in the table.

Can anyone explain to me what's going on here and how I might be able to circumvent the problem so that it still sees these as blank fields of zero length?

Thanks,

Rob

 

[chergh]

If a field is blank it can be either null or a zero length string. Sounds like your expecting your fields to be a zero length string when they're actually nulls.

You could use an update query to change the nulls to a zls.

 

[psu_1995]

ok, thanks for your help. I figured out how to test for a null. Not sure if what I did is the best way to code this solution, but I'm posting my code below for the benefit of others who might read this post.

Set rst2 = dbs.OpenRecordset(table15, dbOpenDynaset)


handle_null:

rst2.MoveFirst
If IsNull(rst2![AMID_from_mstr_amid_tble]) = True Then
      With rst2
             .Edit
            ![AMID_from_mstr_amid_tble] = ""
             .Update
        End With
End If
Do While Not rst2.EOF
     rst2.MoveNext
     If Not rst2.EOF Then
      If IsNull(rst2![AMID_from_mstr_amid_tble]) = True Then
       With rst2
             .Edit
            ![AMID_from_mstr_amid_tble] = ""
             .Update
        End With
      End If
     End If
Loop

:Return

 

[chergh]

Why not just:

strSQL = "UPDATE table15 " & _
"SET table15.AMID_from_mstr_amid_tble = "" " & _
"WHERE table15.AMID_from_mstr_amid_tble Is Null"

strSQL.execute