CODEWITHSUNDEEP
ms-access
Lee Mac
Lee Mac

Reputation: 16015

MS Access Selecting Blank/Empty/Null Text Records

I'm trying to ascertain the best way to test for blank/empty/null text records.

By this I mean text records which are either:

I've experimented with varying query criteria using the following table (named Table1):

Text Table

Here, the Spaces record contains an arbitrary number number of spaces, the Empty String record contains an empty string (""), and the Null record is just that.

My first thought to achieve my goal was to use a query such as:

SELECT * FROM Table1 WHERE TextField IS NULL OR Trim(TextField)=""

And this indeed returns the three target records: Spaces, Empty String & Null.

However somewhat oddly, using:

SELECT * FROM Table1 WHERE TextField = ""

Returns both the Empty String record and the Spaces record:

Spaces AND Empty String

Which leads me to think that my query can omit the Trim function and become:

SELECT * FROM Table1 WHERE TextField IS NULL OR TextField=""

Alternatively I considered using:

SELECT * FROM Table1 WHERE Nz(TextField)=""

EDIT: To be specific, my question is ultimately:

What is the best way to select blank text records?

Upvotes: 7

Views: 62211

Answers (2)

Erik A
Erik A

Reputation: 32642

For normal text, Access handles strings with only spaces as empty strings (e.g. SELECT " " = "" returns -1 = True).

This means that the solution introduced by June7, WHERE TextField & "" = "", is likely the most efficient solution. Another contender is the Nz function, which is handled by the database engine and somewhat optimized.

When using indexes, however, both string concatenation and function calls invalidate the index. WHERE TextField IS NULL OR TextField="" doesn't, and will be the fastest by far if TextField is indexed.

If you bring rich text into the mix, you're not going to get away with anything but casting it to normal text first. In Access 2016, when you enter a space in a rich text field, it actually contains the following: <div>&nbsp;</div> (you can see this by using RichTextField & "").

For a rich text field, indexes are not going to work anyway, so you can use the following:

WHERE PlainText(RichTextField) & "" = ""

Upvotes: 8

Jeffrey
Jeffrey

Reputation: 538

Nz(TextField)=""

Is the approach most often used when dealing with nulls and empty strings.

In Access, The Long Text data type will automatically be trimmed to save space, this is why Trim(TextField) is the same as TextField. If you ever convert it to a Rich Text data type, these will be different. In that case:

TRIM(Nz(TextField))=""

Should cover all your bases.

Upvotes: 2

Related Questions