Successful DLOOKUP fails to return empty strings

[Roger Neame]

If a table field is configured to ALLOW zero length strings and NOT ALLOW
nulls, then you would think that a DLOOKUP function that successfully finds
the appropriate record would return the zero length string rather than
convert it to a NULL. Why the function converts the found zero-length string
data into a null is a mystery. In contrast, a query will return the
zero-length string without converting it to a null.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...104b&dg=microsoft.public.access.modulesdaovba

 

[Allen Browne]

You are correct, Roger.

DLookup() fails to recognise a ZLS, and wrongly reports it as a Null.

I reported this to Microsoft years ago, but after 15 years of Access, it's
never been fixed.

In the mean time, here's a replacement that does handle the ZLS correctly,
runs faster than DLookup(), and allows you to specify which value you want
if there are several matches:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html