Trying to get results from a query where the criteria for a memo field is set to "Is Null" and it doesn't work...

Using Access 2016 and the query says it fails.  I tried the function of Cstr([MemoField]) with "Is Null" for the criteria and still no luck.  Any suggestions?
Thanks, Kevin
itsquadSystems AdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:

Beware that the type convertion function don't accept null value.
Plus manipulating memo fields with strings can lead to unexpected behaviors, such as the result beeing shrunk to 255 characters.

Did you try this ?
[MemoField] IS NULL
Jeffrey CoachmanMIS LiasonCommented:
memo field
Do you mean "Long Text"?

Why is cstr() needed?
The query should work well without it...:

SELECT Field1, MemoField
FROM YourTable
Where MemoField IS Null

Am I missing Something?

Jeffrey CoachmanMIS LiasonCommented:
FWIW,  ...I could only test this in Access 2013.
Another Expert will have to confirm what we posted for Access 2016, be sure...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
have you tried NZ function?

Nz Function

but what is actually you want to achieve here?
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Ok, my guess is that your memo field either has "Allow Zero Length" strings set to true, or the field contains either blanks, or non-printable characters.

Cstr will return a STRING variable. This also means that cstr(Null)="" (empty string), however that is still a string, and not the same as null.

I personally for this exact reason always set my string fields to not allow zero length strings.

So I would suggest setting that value to False. If that is not a feasible solution for you, you could try this sql
SELECT Field1, MemoField
 FROM YourTable
 Where MemoField IS Null or MemoField=""

Open in new window

This doesn't solve the issue of strings that contain blanks. Access is smart enough so that if you enter just blanks in a field, access will trim it for you. But if we import data from external sources, sometimes we can get "bad" data into teh database in that way. We could then use the following SQL
SELECT Field1, MemoField
 FROM YourTable
 Where trim(MemoField &"")=""

Open in new window

(Note that the Null case is handled through the string concatenation.

Finally, it happens that we have imported (or copy pasted) a non-printable character. I've only seen it occur maybe a dozen times, and when it does it is usually something I fix manually.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Solution posted, no activity from OP
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.