435,264 Members | 1,079 Online
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

When Null field = Null field, the results are FALSE?

 100+ P: 133 When testing the following code, the results should be true, however, a false results is generated. I don't understand why and i need to maintain the data values. Expand|Select|Wrap|Line Numbers      If strTEMP_CORD_No = rstTemp!CORD_No Then             strTEMP_BANNERPOAmt = strTEMP_BANNERPOAmt + rstTemp!BANNERPOAmt         Else             MsgBox "cord no not equal"         End If   Mar 5 '12 #1
4 Replies

 Expert Mod 10K+ P: 12,366 You have to use the IsNull or Nz functions if you want to compare nulls. Null fields can be anything, so they never match each other in a binary comparison. Which is what the = binary comparison operator does. Mar 5 '12 #2

 100+ P: 133 i modified the code using the IsNull in the following matter. Expand|Select|Wrap|Line Numbers      If IsNull(strTEMP_CORD_No) Then             If IsNull(rstTemp!CORD_No) Then                     strTEMP_BANNERPOAmt = strTEMP_BANNERPOAmt + rstTemp!BANNERPOAmt             End If         Else             If strTEMP_CORD_No = rstTemp!CORD_No Then                 strTEMP_BANNERPOAmt = strTEMP_BANNERPOAmt + rstTemp!BANNERPOAmt             End If         End If   Mar 5 '12 #3

 Expert Mod 10K+ P: 12,366 You could also use Nz Expand|Select|Wrap|Line Numbers If Nz(Field1, "") = Nz(Field2, "") Then    ' Do something End If Mar 5 '12 #4

 Expert Mod 15k+ P: 31,494 Is this a serious question? You are asking why the result of comparing one entirely unspecified value with another equally unspecified value doesn't return a True result when you expect it to. Good luck with that. Mar 5 '12 #5