OK, this isn't so much a question as an observation for the benefit of the community (and myself when I later forget and come back here to search).
As many of you know (and for those who don't I highly recommend learning about this), Access treats an empty string and a NULL as two very different entities. A NULL is the lack of a value in a field, whereas an empty string is a valid value of a 0-length string. To put them in parentheses an NULL would be () and an empty string would be ("").
The Nz function is commonly used to convert NULLs to empty strings or other values. The REQUIRED property of a field determines if it can be NULL while the ALLOW ZERO-LENGTH STRINGS property determines if it can be an empty string.
To further illustrate the differences, go to the Immediate Pane in Access's VBA editor window and type the following:
? "" = ""
(Result is true)
? "" = NULL
(Result is NULL)
OK, I think I've established the differences. However, DLookup seems to be unable to return an empty string. If the result is an empty string, it instead returns a NULL.
To illustrate, create a table (tblTable) with and ID primary key field (type Autonumber will work for this illustration) and 3 text fields (txtNull, txtEmpty, and txtData) setting all 3 to not be required and to allow zero-length strings. Create a record in the table (we'll assume ID = 1) skipping the txtNull field (leaving it as null), entering "" into txtEmpty (a zero-length string), and entering some sort of data into txtData.
Now, play around with DLookup. Try the following:
? DLookup("txtNull", "tblTable", "ID = 1")
(returns Null)
? DLookup("txtEmpty", "tblTable", "ID = 1")
(returns Null)
? DLookup("txtData", "tblTable", "ID = 1")
(returns data)
? IsNull(DLookup("txtNull", "tblTable", "ID = 1"))
(returns True)
? IsNull(DLookup("txtEmpty", "tblTable", "ID = 1"))
(returns True)
? IsNull(DLookup("txtData", "tblTable", "ID = 1"))
(returns False)
? DLookup("IsNull([txtNull])", "tblTable", "ID = 1")
(returns True or -1)
? DLookup("IsNull([txtEmpty])", "tblTable", "ID = 1")
(returns False or 0)
? DLookup("IsNull([txtData])", "tblTable", "ID = 1")
(returns False or 0)
Now, as you can see, if you check for NULL before it is returned, it correctly differentiates. However, if you check the returned result, it returns a NULL instead of "".
I spent a couple of hours tracing a phantom bug the other night because of this, but I couldn't find any mention of it on Tek-Tips, so I thought I'd share.
As many of you know (and for those who don't I highly recommend learning about this), Access treats an empty string and a NULL as two very different entities. A NULL is the lack of a value in a field, whereas an empty string is a valid value of a 0-length string. To put them in parentheses an NULL would be () and an empty string would be ("").
The Nz function is commonly used to convert NULLs to empty strings or other values. The REQUIRED property of a field determines if it can be NULL while the ALLOW ZERO-LENGTH STRINGS property determines if it can be an empty string.
To further illustrate the differences, go to the Immediate Pane in Access's VBA editor window and type the following:
? "" = ""
(Result is true)
? "" = NULL
(Result is NULL)
OK, I think I've established the differences. However, DLookup seems to be unable to return an empty string. If the result is an empty string, it instead returns a NULL.
To illustrate, create a table (tblTable) with and ID primary key field (type Autonumber will work for this illustration) and 3 text fields (txtNull, txtEmpty, and txtData) setting all 3 to not be required and to allow zero-length strings. Create a record in the table (we'll assume ID = 1) skipping the txtNull field (leaving it as null), entering "" into txtEmpty (a zero-length string), and entering some sort of data into txtData.
Now, play around with DLookup. Try the following:
? DLookup("txtNull", "tblTable", "ID = 1")
(returns Null)
? DLookup("txtEmpty", "tblTable", "ID = 1")
(returns Null)
? DLookup("txtData", "tblTable", "ID = 1")
(returns data)
? IsNull(DLookup("txtNull", "tblTable", "ID = 1"))
(returns True)
? IsNull(DLookup("txtEmpty", "tblTable", "ID = 1"))
(returns True)
? IsNull(DLookup("txtData", "tblTable", "ID = 1"))
(returns False)
? DLookup("IsNull([txtNull])", "tblTable", "ID = 1")
(returns True or -1)
? DLookup("IsNull([txtEmpty])", "tblTable", "ID = 1")
(returns False or 0)
? DLookup("IsNull([txtData])", "tblTable", "ID = 1")
(returns False or 0)
Now, as you can see, if you check for NULL before it is returned, it correctly differentiates. However, if you check the returned result, it returns a NULL instead of "".
I spent a couple of hours tracing a phantom bug the other night because of this, but I couldn't find any mention of it on Tek-Tips, so I thought I'd share.
