selective updating of fields that are NULL with module 1

[hwkranger]

Can someone please tell me why my If statements inside the .edit/.update statements refering to a null string are always "false" even if the field is null. I've also tried to use NULL instead of "" (that's how i first tried the code) anyways.. this code works other than the if statements, I originally had this modifying the records in a query that was generated, but now I want to remove the query and use the module by it determining if the fields are empty.

If myRecordSet.BOF = True Then Exit Sub
With myRecordSet
    If .RecordCount Then
        .MoveFirst
        Do Until myRecordSet.EOF

        .Edit
        If !Opening = "" Then
        !Opening = "Enclosed is a copy of the check we used to pay the " & month & " " & year & " invoice, which you reported not receiving, please refer to the following information."
        End If
        
        If !Signature = "" Then
         !Signature = Name
        End If
        
        If !Closing = "" Then
         !Closing = "Thank you for looking into this matter, if you have any problems, please feel free to call me at XXX-XXX-XXX."
        End If
        
        .Update
myRecordSet.MoveNext
Loop
End If

Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[JeremyNYC]

Because null is not the same as "". Null means that there is nothing there. "" means that there is a zero-length string there.

Doing
If nz(!Signature) = "" Then
will get the job done.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cmmrfrds]

Null is not the same as empty string - need to check separate.

If !Opening = "" or isnull(!Opening) Then

 

[hwkranger]

Perhaps I need a clarification on the difference on Null and zero length string. If a field has never had any input, is it zero or null?

Thankyou for the information, I'm now know that i need to check for both. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[cmmrfrds]

There is a very good explanation in Access Help about nulls and zero length strings under.

About working with blank fields in queries