MS Access Selecting Blank/Empty/Null Text Records

Question

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:

• Null
• An Empty String ("")
• Any number of spaces (e.g. " ")

---

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

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:

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

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

• But is this reliable?
• Is this best practice when selecting empty text records?

---

Alternatively I considered using:

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

• Are there drawbacks to this approach?
• Are there better ways to achieve this?

---

EDIT: To be specific, my question is ultimately:

What is the best way to select blank text records?

Baptlsta

I count at least 5 separate questions. Try to ask a more specific question. – Farthingale

I have edited the question to hopefully be more specific with my line of enquiry. – Baptlsta

Why is this data input allowed? I NEVER allow empty strings by table design. In my experience, Access automatically trims following spaces which means no fields have just 'any number of spaces'. That means I only have to test for Null. Because some would argue against using Nz() - a VBA function call - another option: WHERE TextField & "" = "" – Zoie

@June7, I agree with your sentiments and appreciate your suggestion, however these records reside in an ODBC database whose design is outside of my control; furthermore, much of the data in said database has been migrated from the backend databases of previous systems ad nauseum. – Baptlsta

Okay, Access links to data. You said reference to TextField is same result as Trim(TextField) so Access is dropping spaces? Or try: Trim(TextField) & "" = "" – Zoie

@Zoie Yes, it seems that Access is automatically dropping spaces when evaluating queries, however, the results still contain spaces (per my screenshot above). I found this behaviour unexpected and so wanted to ask for best practices. – Baptlsta

The fewer function calls in query the better. – Zoie

Answer 1

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) & "" = ""

Farthingale

Thank you Erik for your comprehensive answer, I appreciate your thoroughness. I'm rather surprised that null & "" is valid, but then VBA seems very forgiving. – Baptlsta

It's not that unusual. Some SQL variants, like T-SQL, have a concatenate null yields null setting that can be on or off (it's deprecated nowadays). For Access, concatenating null to a string always yields that string – Farthingale

Answer 2

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.

Millenarianism

Or Trim(TextField) & "" = "" - one less function call. – Zoie

@Millenarianism Thank you for your answer. I noticed that Short Text fields (or just Text fields in Access 2007) would also automatically remove trailing spaces on data entry, however, I wasn't certain whether such behaviour was reliable when working with databases in which the tables already contains records which only contain spaces. I was also unsure of the result of supplying Trim with a null value as opposed to a string. – Baptlsta

Trim(Null) returns Null. – Zoie