DLookup returning Null instead of empty string

[pdanes]

I have a perfectly ordinary DLookup instance locating a record by an autonumber code. The field I want to retrieve is defined as required, empty string allowed. The record in question has exactly that - an empty string. But when I locate the record and request the field with DLookup, it returns a Null instead of the empty string that is actually there.

To forestall questions about my senility and drug use, yes, beyond question, the record DOES exist and the field DOES have an empty string, not a Null. A SQL query retrieves the information properly, but the DLookup function turns the empty string into a Null before passing it off to my calling code. I find no mention of such behavior in any documentation of the DLookup function. The DCount function, when the contents of this field are included in the search criteria properly informs me that there is ONE record with that field = '' and ZERO records with that field Is Null.

It's easy enough to deal with now that I know what is happening, but I find this odd. Has anyone run into such behavior?

 

[theDBguy]

That is interesting. Are you able to post a sample db to demo the problem?

 

[MajP]

I believe this is a known problem.

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

Microsoft Access tips: Extended DLookup()

Replacement for DLookup() in Microsoft Access. The replacement is faster, handles nulls correctly, and lets you specify which record you want returned (by specifying a sort order.)

allenbrowne.com

 

[pdanes]

I believe this is a known problem.

Microsoft Access tips: Extended DLookup()

Replacement for DLookup() in Microsoft Access. The replacement is faster, handles nulls correctly, and lets you specify which record you want returned (by specifying a sort order.)

allenbrowne.com

Ah, yes - I missed that. Point #4.

 

[pdanes]

That is interesting. Are you able to post a sample db to demo the problem?

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.

 

[pdanes]

I'm going to make a crazy suggestion. Fix the underlying problem. It makes no sense to define a text field as required but to allow ZLS. Change the setting to not allow ZLS. Then the field will never be "empty".

You don't know how this app works. It does make sense. I initially had it defined as allowing nulls, but that caused a number of problems that this definition fixed.

There is no 'underlying problem'. DLookup incorrectly returns a different value than what is actually stored in the table. That is a coding error by Microsoft, not a design error on my part.

 

[pdanes]

I'll say it again. It makes no sense to allow ZLS for a required field. ZLS is essentially a "blank". It is just another version of "nothing". If you make the field required, Access will not allow you to save a record with a null value so I don't really know how you could have a problem with null when the field is required. If you never save a record with a ZLS, you never have to find it and fix it.

Yes, the DLookup() should return the actual value.

Yes, I got it the first time. And you're wrong - it does make sense. Saying that it makes no sense when you have not the slightest clue what I am doing with it is what doesn't make sense. I would have expected that you would understand the difference between an empty string and a null. They are vastly different entities, and not at all interchangeable.

I am not 'fixing' the blank field - there is nothing wrong with it. It is blank, zero-length, not null, exactly as it is supposed to be. I am looking for it for a specific purpose.

 

[pdanes]

The disconnect is setting the field to be required but allowing it to contain nothing. Why would you require a field but let it be empty? I am not saying that ZLS and Null are the same. They are not. They are two different versions of nothing. Excel treats them the same. Access treats them differently. That confuses everything and that is why i never allow ZLS. I prefer that empty fields always be null whether they are text, numbers, or dates. That simplifies query logic and I don't have to remember what data type some obscure field is so I have to worry about dealing with ZLS in addition to Nulls.

If you want ZLS, that is up to you. But, I guess you'll need to deal with the DLookup(), You can change it to:

Nz(DLookup("xxx", "xxxx", "xxx"), "")

That will return ZLS instead of Null so the fix is simple once you understand the bug and the fix won't break if MS fixes the bug so that's nice too.

I require a field but let it be empty because that is what works for the data I am dealing with. I could let it be null, but that requires extra work for dealing with nulls, which is what I specifically designed to avoid. I had it that way and was constantly having to add some version of "Or Is Null" to almost everything that operated on that field. This avoids all that.

You wrote: ZLS is essentially a "blank". It is just another version of "nothing". It is, which is the whole point of my design. I want a valid string, strictly defined as zero length, just like a numeric value of zero. A null is an undefined value. My value in these cases is NOT undefined - it is known, and it is known to be blank.

 

[pdanes]

You clearly have made up your mind that using a ZLS is correct so I will not try to explain why I disagree.

You have a solution so it would be appropriate to close the thread and move on.

ZLS is neither 'correct' nor 'incorrect'. It is a design choice, and one that works well for me - better than nulls. I had those initially, because I agree that nulls are generally the preferred method for such situations, but I abandoned them because of the problems they were causing. If you understood what I was dealing with in this case, you might agree. Or might not - you might prefer the hassles of nulls to my solution.

I generally explain nulls to non-IT types by equating them to a measured distance:
A known distance to something is the equivalent of a number, or a string of text.
An object touching the source is distance zero, or an empty string.
A null means we have no idea where the object is, so talking about a distance is meaningless. You can't say it's equal to zero, you can't say it's not equal to zero, you can't say anything about its distance from the source, because you don't know where it is.

You wrote: They are two different versions of nothing. No, they are not. An empty string is nothing. A null is not 'nothing' - it is undefined.

An empty string properly expresses the concept of nothing. A null is simply unknown.

 

[pdanes]

Sorry, but nulls are no problem if you understand how to use them. This conversation is pointless. You have an opinion and you're stickin' to it. It works for you. Go in peace.

I do understand how to use them. You didn't seem to, since you wrote incorrect statements like claiming a null is a different version of nothing, which is why I took the trouble to explain some of my use case. But I agree that what you have contributed to this conversation is pointless.

 

[pdanes]

An explanation that the typical poster here can understand. You said you couldn't use nulls, they didn't work for you so now you're telling me you're the expert. OK, you're the expert.

"An explanation that the typical poster here can understand." Now you're adding condescension to your endearing traits? I have a better opinion of people on this forum than you apparently do - I'm sure people here could understand an accurate explanation of nulls, like my comparison to a measured distance. I'm also sure that most people on here, except maybe the beginners, do not need such an explanation, since they already understand nulls quite well. In any case, an incorrect explanation, like the one you posted, serves nobody.

And you hallucinating things I didn't say and then lambasting me for them is just plain obnoxious. You're starting to sound like a troll on Facebook who has nothing better to do with their life than look for people to insult.

I did not write that I couldn't use nulls. I wrote that they caused problems and I thought up a better way of dealing with my data, which avoided those problems.

I did not write that they "didn't work" for me. That's a completely nonsense statement. I wrote that an empty string more accurately reflects the situation of my user's information.

I did not write that I am an expert. I wrote that I know what I am doing with this data and that my design suits it better than the nulls that you keep harping on. Although, in light of the information content in your last few posts, I am starting to think that compared to you, I might indeed be the expert.

 

[plog]

We're all riveted by this.

Now, it's too late for either of you to win this conversation by knockout, but whoever abandons this internet slapfight first can win by a split decision. Who's gonna blink first and take the high road? Now remember, you lose points if you actually post and state your intention of letting the other have the last word. You just have to let it go to be semi-victorious.

 

[pdanes]

Are you having fun berating me?

No, actually - you're a PIA, and I wish you would go away.

I'm not sure who's hallucinating.

No doubt, but I, on the other hand, am sure - you are. You have dreamed up all sorts of thing I haven't said.

Sounds to me like a novice who doesn't understand how to use nulls.

That probably because you apparently don't understand anything I've written, given how you respond to it.

But you insist that you're an expert.

And exactly THAT is one of the many things you're hallucinating.

OK. Who can argue with that? You're a legend in your own mind.

And you're an obnoxious busybody. Not one thing you have written has been the slightest use, ever, that I can recall. You always butt in with no answer the question I post, but a useless opinion on how whatever it is I'm doing is all wrong and YOU would never do it that way.

You've been trolling this forum for 13 years and have the huge post count of 115.

I am USING this forum to get answers to questions I get stuck on, and occasionally answer someone else's, exactly as such forums are meant to be used. If anyone is acting like a troll here, it's you. But I'm glad to see that I interest you enough to go look such things up for me.

Guess YOU can't be bothered to help anyone else or maybe you don't actually know enough.

I do, sometimes. But I have other things to do, and often a question I could have answered has already been answered by someone else. I don't get on here all that often.

Even our novices try to answer questions but not you. Hmmmm.

And not you. Not once have you written anything of value in response to one of my posts, just useless opinions and wrong explanations.

Sorry I wasted my time trying to help you.

You did waste my time. You didn't help me, with anything, nor did you even try to.

Given your expertise, you don't need my help.

I most definitely don't need what you try to pass off as help.

You know everything there is to know about nulls except how to actually use them.

And a final hallucination to finish your diatribe, with a final lack of understanding, again. You're a gem.

 

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