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.

 

[Pat Hartman]

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

 

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

but I abandoned them because of the problems they were causing.

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.

 

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

 

[Pat Hartman]

since you wrote incorrect statements like claiming a null is a different version of nothing,

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.

 

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

 

[Pat Hartman]

Are you having fun berating me?

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.

I'm not sure who's hallucinating. Sounds to me like a novice who doesn't understand how to use nulls. But you insist that you're an expert. OK. Who can argue with that? You're a legend in your own mind. You've been trolling this forum for 13 years and have the huge post count of 115. Guess YOU can't be bothered to help anyone else or maybe you don't actually know enough. Even our novices try to answer questions but not you. Hmmmm. Sorry I wasted my time trying to help you. Given your expertise, you don't need my help. You know everything there is to know about nulls except how to actually use them.

 

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