Successful DLOOKUP fails to return empty strings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger Neame - 28 Nov 2006 20:02 GMT

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.

Reply to this Message

Allen Browne - 29 Nov 2006 02:20 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 14 lines]
>
> http://www.microsoft.com/office/community/en-us/default.mspx?mid=3eb91502-faad-4
d4e-a409-b55824d8104b&dg=microsoft.public.access.modulesdaovba

Reply to this Message