DLookup returning Null instead of empty string

[June7]

Documented or not, appears to be normal behavior.

I've never noted this because I do not allow empty string in fields. I prefer to deal with Null. It's what Nz() is good for. What difficulties do you have with Null that cannot be dealt with?

If I make a field required then there will be data, not ZLS nor Null.

 

[pdanes]

Documented or not, appears to be normal behavior.

I've never noted this because I do not allow empty string in fields. I prefer to deal with Null. It's what Nz() is good for. What difficulties do you have with Null that cannot be dealt with?

If I make a field required then there will be data, not ZLS nor Null.

Thank you, but I do not have any difficulties that cannot be dealt with. It was just simpler to code and display data to the user without having to constantly remember to put in the Nz() function, or add 'Or Is Null' to SQL statements. And again, a Null indicates that a value is unknown, which is not the case here. The values ARE known in the case here where I have specified the empty string, and they are known to be blank. An empty string both makes the coding simpler and more accurately reflects the situation of the user's actual information.

I just ran into this DLookup conversion and was puzzled by it. But I shouldn't have been - the page MajP pointed out to me is one that I have seen many times in the past, but somehow spaced over the fact that it specifically mentions this behavior. I'd never encountered it before, because I usually do use nulls for missing information. This was a somewhat extraordinary case.

 

[pdanes]

Documented or not, appears to be normal behavior.

I've never noted this because I do not allow empty string in fields. I prefer to deal with Null. It's what Nz() is good for. What difficulties do you have with Null that cannot be dealt with?

If I make a field required then there will be data, not ZLS nor Null.

Curious, though - what do you mean by normal behavior? If you mean that DLookup is known to do this, then yes. If you mean that is is supposed to be doing this, what do you base that on? It does not seem to me that changing found data is proper behavior for any code.

 

[gemma-the-husky]

@pdanes

In fact, if your field is required, but allows a zls, then presumably it can never be null.

So why can't you you just use this sort of thing when using a table of this type. I often do this anyway with dlookups to avoid a null response.

NZ(dlookup(blah,blah),"""")

 

[pdanes]

@pdanes

In fact, if your field is required, but allows a zls, then presumably it can never be null.

So why can't you you just use this sort of thing when using a table of this type. I often do this anyway with dlookups to avoid a null response.

NZ(dlookup(blah,blah),"""")

I can. The point of the design I selected was that I didn't have to. The incorrect response of the DLookup function means that I now DO have to again, or figure out some other way to retrieve my non-null empty string. When I have an empty string, I expect the DLookup function to get it for me, not turn it into a null. It is literally finding the string and then turning that found data into something that is NOT in the table before handing it to me.

Suppose you asked a function to find the name 'Smith', and it did so, then turned it into 'Jones' before giving it to you. That is what is happening, and I don't understand why MS doesn't repair it. It has apparently been known for quite some time, and it is simply wrong. It can't be that hard to fix.

 

[Josef P.]

If an empty string is stored in a varchar data field, I would also expect an empty string to be returned.
If you query the data with a DAO.Recordset (OpenRecordset + read Field.Value), an empty string and not Null is returned.
So Access.DLookup behaves differently from the DAO library.

Note: I have to admit that I have never noticed this before as I use my own record replacement function instead of Access.DLookup.
In addition, I usually use a zero for "no input" and a visible special character if the user makes an entry that should actually mean "empty" or "no value available", so that you can recognize the difference in the input form. But even this rarely happens, as I prefer to provide the user with selection tables (if possible).

 

[pdanes]

If an empty string is stored in a varchar data field, I would also expect an empty string to be returned.
If you query the data with a DAO.Recordset (OpenRecordset + read Field.Value), an empty string and not Null is returned.
So Access.DLookup behaves differently from the DAO library.

Note: I must confess that I have never noticed this before, as I use my own recordset replacement function instead of Access.DLookup.

Yes, DLookup does behave differently from DAO, and I believe it is simply wrong. I cannot imagine any justification for a search function returning data that does not exist.

 

[gemma-the-husky]

I can. The point of the design I selected was that I didn't have to. The incorrect response of the DLookup function means that I now DO have to again, or figure out some other way to retrieve my non-null empty string. When I have an empty string, I expect the DLookup function to get it for me, not turn it into a null. It is literally finding the string and then turning that found data into something that is NOT in the table before handing it to me.

Suppose you asked a function to find the name 'Smith', and it did so, then turned it into 'Jones' before giving it to you. That is what is happening, and I don't understand why MS doesn't repair it. It has apparently been known for quite some time, and it is simply wrong. It can't be that hard to fix.

It might actually be hard to fix. The real issue is that it's an undocumented feature. I bet it took a while for you to realise it was failing.

On a vaguely similar thing, I was trying to use a date field in an order to limit a later action. If the date field had a value, then the delivery has to take place on the specified date. If it was null, it could be supplied on any date. The problem was I wanted the order to be unique, but I found out that access has a different concept of uniqueness where nulls are involved. I ended up having to use date 0 (which is obviously 1899), and specifically test for date 0 in my delivery date code. (Analogous to the issue the 0/null is causing you). Maybe I could have added a "specific date only" flag as well as the date field, but I thought the null itself would accomplish that.

 

[Josef P.]

The conversion from '' to Null may take place when the value is returned.

Short test:

Value VarCharField (id=1): '' (ZLS)
Value VarCharField (id=2): Null

?DLookup("VarCharField + 'a'", "TestTab", "id=1")
=> a

?DLookup("VarCharField + 'a'", "TestTab", "id=2")
=> Null

Assuming that no space is used at the beginning or end of a value:

? Trim(DLookup("VarCharField + ' '", "TestTab", "id=1"))
=> ''
? Trim(DLookup("VarCharField + ' '", "TestTab", "id=2"))
=> Null

BTW:
DMax/DMin return also Null instead of '' (ZLS).

 

[pdanes]

It might actually be hard to fix. The real issue is that it's an undocumented feature. I bet it took a while for you to realise it was failing.

On a vaguely similar thing, I was trying to use a date field in an order to limit a later action. If the date field had a value, then the delivery has to take place on the specified date. If it was null, it could be supplied on any date. The problem was I wanted the order to be unique, but I found out that access has a different concept of uniqueness where nulls are involved. I ended up having to use date 0 (which is obviously 1899), and specifically test for date 0 in my delivery date code. (Analogous to the issue the 0/null is causing you). Maybe I could have added a "specific date only" flag as well as the date field, but I thought the null itself would accomplish that.

It didn't take too long. It was in a simple search routine populating some visuals on a form. When they didn't populate correctly, I started digging into the code, and soon enough, I got to where I typed ?DLookup(blah) into the immediate window, and got Null. I then started poking around some more, using DCount and specifying the search parameters, including ='', and found that the record did exist, but DLookup was changing my blank into a null, which is when I started this thread.

I can't believe it would be hard to fix, though. I'd guess MS maybe figures it has been that way for so long they that they might break a lot of user code if they fixed it, so they just leave it with the usual "Microsoft has confirmed that this a problem" dodge.

 

[pdanes]

The conversion from '' to Null may take place when the value is returned.

Short test:

Value VarCharField (id=1): '' (ZLS)
Value VarCharField (id=2): Null

?DLookup("VarCharField + 'a'", "TestTab", "id=1")
=> a

?DLookup("VarCharField + 'a'", "TestTab", "id=2")
=> Null

Assuming that no space is used at the beginning or end of a value:

? Trim(DLookup("VarCharField + ' '", "TestTab", "id=1"))
=> ''
? Trim(DLookup("VarCharField + ' '", "TestTab", "id=2"))
=> Null

BTW:
DMax/DMin return also Null instead of '' (ZLS).

Yes, it does, and I am convinced it should not. Didn't know min and max also did that, though - thanks.

 

[DickyP]

Yes - here is a DB with a single table with a single record, as I described. There is one small VBA module that looks at that table in various ways, and shows the results.

Just had a look at example the database that was attached to the post. The field is, as reported, Required and Allow Zero length Strings. I would, however, having seen your usage, expected it to have a default value of "".

The next question is how are you creating the records and are you explicitly assigning "" to the field? Or are they being imported?

The Stuff in the record shown is not a 'normal' Zero length string vbNullstring, but appears to be an empty string vbNullChar. These work differently according the background API calls involved in the command being used, and vbNullChar is problematical to use to say the least.

 

[pdanes]

Just had a look at example the database that was attached to the post. The field is, as reported, Required and Allow Zero length Strings. I would, however, having seen your usage, expected it to have a default value of "".

The next question is how are you creating the records and are you explicitly assigning "" to the field? Or are they being imported?

The Stuff in the record shown is not a 'normal' Zero length string vbNullstring, but appears to be an empty string vbNullChar. These work differently according the background API calls involved in the command being used, and vbNullChar is problematical to use to say the least.

The real one does have such a default value. This was just an example I knocked together to answer DBGuy's question.

The blanks in my real DB are strictly created by code, though, under a particular set of circumstances, never imported or left to the user, so a default value isn't really necessary there. But I put it in just as a matter of form.

I don't know about vbNullChar. I would expect vbNullstring - a zero-length, VarType 8 text string. A vbNullChar is not an empty string, but a string of length one, containing a single character of ASCII value zero. There is no scenario in which I would expect that, but there is also no scenario in which I would expect a null. No telling what goes on in the evil minds of the MS coders.

 

[DickyP]

The problem with vbNullChar is that although it is a char it returns Null in DLookup. As I said its use is problematic. If you ever get to see a copy 'Dan Watermans guide to the Win 32 API' it is replete with waring to only use it when you need to generate a zero terminated string, because of its odd behaviour.

 

[pdanes]

The problem with vbNullChar is that although it is a char it returns Null in DLookup. As I said its use is problematic. If you ever get to see a copy 'Dan Watermans guide to the Win 32 API' it is replete with waring to only use it when you need to generate a zero terminated string, because of its odd behaviour.

Hm. If both ZLS and vbNullChar return Null, and DMin and DMax do so as well, that would seem to point more to a conscious choice rather than a coding error. But I can't imagine the justification for intentionally designing such behavior. Do you have any idea?

 

[Josef P.]

My personal opinion on the problem: it will remain as it is.
However, I would recommend the recordset replacement functions for DLookup & Co. anyway and then the expected behavior will occur.

[a bit OT]

The Stuff in the record shown is not a 'normal' Zero length string vbNullstring, but appears to be an empty string vbNullChar.

Hmm ... if I check the value of Stuff from the database in #5, it doesn't match with vbNullChar.

With CurrentDb.OpenRecordset("select Stuff from Tabulka1")
    Debug.Print "is null: "; IsNull(.Fields(0).Value)
    Debug.Print "vbNullChar: "; StrComp(.Fields(0).Value, vbNullChar, vbBinaryCompare)
    Debug.Print "vbNullString: "; StrComp(.Fields(0).Value, vbNullString, vbBinaryCompare)
    Debug.Print "ZLS: "; StrComp(.Fields(0).Value, "", vbBinaryCompare)
    .Close
End With
=>
is null: False
vbNullChar: -1
vbNullString:  0
ZLS:  0

 

[DickyP]

Hold on - I'll disembowel a sacrifice or read the runes to try and see what was in the minds of the designers - can see any other way to find out. I certainly can't see a logical reason.

As a check / confirmation I tried the equivalent with one of my database and got the following which was what we'd expect. The field PicturePath is Required = Yes, Allow Zero Length = Yes , and Default Value of "".

? Dcount ("*","tblBook","PicturePath = '' " )
972
? Dcount ("*","tblBook","PicturePath > '' " )
217

So I'm even more confused.

 

[isladogs]

In case you haven't already tested this, I added two more DLookup checks which confirm inconsistency in how DLookup handles ZLS:

Debug.Print "DLookup of field: "; DLookup("Stuff", "Tabulka1", "ID = 1")
Debug.Print "DLookup of ZLS ID: "; DLookup("ID", "Tabulka1", "Stuff = ''")
Debug.Print "DLookup of Null ID: "; DLookup("ID", "Tabulka1", "Stuff Is Null")

Results:
DLookup of field: Null
DLookup of ZLS ID:  1
DLookup of Null ID: Null

 

[pdanes]

Hold on - I'll disembowel a sacrifice or read the runes to try and see what was in the minds of the designers - can see any other way to find out. I certainly can't see a logical reason.

As a check / confirmation I tried the equivalent with one of my database and got the following which was what we'd expect. The field PicturePath is Required = Yes, Allow Zero Length = Yes , and Default Value of "".

? Dcount ("*","tblBook","PicturePath = '' " )
972
? Dcount ("*","tblBook","PicturePath > '' " )
217

So I'm even more confused.

Maybe you have to sacrifice an iPad or iPhone.

As to your examples, I have no idea what is in your table, so I can't comment on the numbers you get. But the search criteria works, as far as I can tell, so if you have 972 records with a blank in that field, and 217 records with something other than a blank, that would fit.

It's only when it returns the CONTENTS of an empty field that it does the incorrect conversion to a null.

 

[pdanes]

In case you haven't already tested this, I added two more DLookup checks which confirm inconsistency in how DLookup handles ZLS:

Debug.Print "DLookup of field: "; DLookup("Stuff", "Tabulka1", "ID = 1")
Debug.Print "DLookup of ZLS ID: "; DLookup("ID", "Tabulka1", "Stuff = ''")
Debug.Print "DLookup of Null ID: "; DLookup("ID", "Tabulka1", "Stuff Is Null")

Results:
DLookup of field: Null
DLookup of ZLS ID:  1
DLookup of Null ID: Null

Yes, those are the results I also get when playing with this. All seems to work properly, except for actually returning the contents of a ZLS. Those it converts to a null.