DLookup returning Null instead of empty string

[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.

 

[cheekybuddha]

I think this has been mentioned before, but this is a known bug[/feature!]

See Allen Browne here

DLookup() has several shortcomings:

1. ...
2. ...
3. ...
4. It returns the wrong answer if the target field contains a zero-length string.

ELookup() addresses those limitations:

1. ...
2. ...
3. ...
4. It correctly differentiates a Null and a zero-length string.

 

[pdanes]

I think this has been mentioned before, but this is a known bug[/feature!]

See Allen Browne h

Yes, MajP pointed that out to me in one of the first responses in this thread.

 

[DickyP]

This whole thread has intrigued me since, as most posters have said, the problem remain 'NOT UNDERSTOOD'. Thinking back to My C++ days I have a feeling that I might be that the API call used by the comparison wll use the empty string which is treated a null in C++, which theAPI is written in. This is just guesswork, I hasten to add!!

Secondly , as an aside why do you (as shown in a couple of the examples) want to look up the value of a value you already know to be ""? Also if you want to know the ID it will only identify the first record that matches, so what do yoywant to do with it?: only the thread starter can identify that but I can't.

 

[The_Doc_Man]

Acting as a moderator, I have to step in for just a moment.

@Pat Hartman and @pdanes got into a discussion that became heated enough to lead to a report for improper behavior after post #20. I am not going to assign blame to someone, because both parties had strong feelings about the correctness of their viewpoints.

When we are dealing with members having a long history and experience with Access and we recognize that the problem under discussion is due to erroneous Access behavior, we should immediately recognize that all bets are off. If there is any blame, point to Microsoft for the incorrect behavior of their function. Be angry with them, not with each other. Once we realize we face incorrect Access behavior, we need a workaround for this behavior. Let us also remember that each of us will choose a workaround that is most comfortable to each of us as individuals and based on our specific situations.

Each person has a degree of "purist" and a degree of "pragmatist" in some proportion. We should recognize that with Access, there is often more than one way to fix a problem. Sometimes it will be a highly technical solution. Sometimes it will be a quick-and-dirty blow-out patch. And we have to try to hold our egos in check if we find that our choice of solution is either incorrect or unpopular or was not well accepted.

Nobody is going to be suspended for this. No threats. Just a request to recognize that we all know different things based on different viewpoints and different experiences. Let each of us be our different selves. Vive le difference.

 

[DickyP]

If I am at fault (I don't think I am but ...)then mea culpa - sorry.

 

[pdanes]

This whole thread has intrigued me since, as most posters have said, the problem remain 'NOT UNDERSTOOD'. Thinking back to My C++ days I have a feeling that I might be that the API call used by the comparison wll use the empty string which is treated a null in C++, which theAPI is written in. This is just guesswork, I hasten to add!!

Secondly , as an aside why do you (as shown in a couple of the examples) want to look up the value of a value you already know to be ""? Also if you want to know the ID it will only identify the first record that matches, so what do yoywant to do with it?: only the thread starter can identify that but I can't.

I don't know the value when I'm doing the lookup. I feed in the ID and expect to get back the value. Sometimes that value is blank, other times it's text. But I focused on the blanks because that is what is misbehaving. When the value is a non-empty text string, DLookup returns it correctly.

Some of the code examples I posted make it look like I already know, but that is because in this test case, I do know - it is the empty string that DLookup does not return corectly. But in my app, in operation, I do not know.

I didn't know about the C++ behavior, but that is certainly a reasonable guess.

 

[pdanes]

Acting as a moderator, I have to step in for just a moment.

@Pat Hartman and @pdanes got into a discussion that became heated enough to lead to a report for improper behavior after post #20. I am not going to assign blame to someone, because both parties had strong feelings about the correctness of their viewpoints.

When we are dealing with members having a long history and experience with Access and we recognize that the problem under discussion is due to erroneous Access behavior, we should immediately recognize that all bets are off. If there is any blame, point to Microsoft for the incorrect behavior of their function. Be angry with them, not with each other. Once we realize we face incorrect Access behavior, we need a workaround for this behavior. Let us also remember that each of us will choose a workaround that is most comfortable to each of us as individuals and based on our specific situations.

Each person has a degree of "purist" and a degree of "pragmatist" in some proportion. We should recognize that with Access, there is often more than one way to fix a problem. Sometimes it will be a highly technical solution. Sometimes it will be a quick-and-dirty blow-out patch. And we have to try to hold our egos in check if we find that our choice of solution is either incorrect or unpopular or was not well accepted.

Nobody is going to be suspended for this. No threats. Just a request to recognize that we all know different things based on different viewpoints and different experiences. Let each of us be our different selves. Vive le difference.

Yeah, sorry about that. I get irked when someone thinks that a helpful contribution is to ignore my question and tell me I am doing it all wrong. And then continue to harp on it when I tell them I know what I want and am doing it that way on purpose.

But you're right - I overreacted. I should have just ignored her completely, and I will try to remember to do that in the future.