Others

How to Query for Null Values and Numeric-Only Text in Access

Adam Davis Oct 21, 2026 869 views

Question details

The user needs to retrieve rows from an Access database where one specific column is completely empty (Null) and another text column contains exclusively numeric characters, excluding alphanumeric strings.

How to Query for Null Values and Numeric-Only Text in Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Filtering a dataset to isolate records with missing data in one field and purely numeric strings in another field, while handling edge cases like scientific notation.
Observed behavior: The user wants to successfully exclude alphanumeric strings like '2lash123' while ensuring valid numeric strings are correctly captured without false positives.

Before you start

Before writing your query, ensure you know the exact field names for the columns you are filtering and understand how your database handles special numeric formats like scientific notation.

Solution 1Recommended

Use Design View Criteria for Null and Non-Alphabetic Text

This is the most straightforward method to filter out empty fields and exclude text containing standard alphabet letters using Access Design View.

This method uses standard Access wildcard characters to exclude basic letters. It is highly effective for basic alphanumeric filtering but may require adjustments if your data contains special symbols or punctuation.

Open Query Design

Open your Access database, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'.

Add Tables and Fields

Add the table containing your data to the workspace. Double-click the two specific columns you want to filter so they appear in the query grid at the bottom.

Apply Is Null Criterion

In the 'Criteria' row for the first column (the one that should be empty), type exactly: Is Null.

Apply Not Like Criterion

In the 'Criteria' row for the second column (the text column), type: Not Like "*[a-zA-Z]*". This excludes any string containing standard letters.

Run the Query

Click the 'Run' button (the red exclamation mark) on the Design ribbon to execute the query and view your filtered results.

Use Design View Criteria for Null and Non-Alphabetic Text

Scientific Notation Warning: This method excludes standard letters but strings like '2E10' might still be interpreted as scientific notation depending on how subsequent functions evaluate them.

Solution 2

Use the IsNumeric Function in SQL View

For a stricter numeric check, you can use SQL view to apply the IsNumeric function alongside the Null check.

Free Microsoft Office alternative

Looking for a Free and Powerful Office Suite? Try WPS Office

While Microsoft Access is useful for complex databases, most everyday data management, analysis, and filtering can be handled brilliantly with spreadsheets. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office, featuring powerful Spreadsheet, Writer, and Presentation tools.

1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
2. Install WPS Office: Run the lightweight installer file to quickly set up the office suite on your device.
3. Open Your Files: Open your existing Microsoft Office files directly in WPS Office to seamlessly continue your data analysis and documentation.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Process large datasets, filter null values, and run complex formulas easily with WPS Spreadsheet.Free to download and use with a highly familiar, intuitive user interface for seamless migration.Lightweight installation that runs smoothly on Windows, Mac, Linux, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Not Like criteria still show records with punctuation?

The criteria *[a-zA-Z]* specifically looks for alphabetical characters from A to Z. It does not check for punctuation marks like hyphens, commas, or slashes. To exclude those, you need to add additional Not Like conditions for specific symbols.

How does Access handle scientific notation like '2E10' in a text field?

Access may treat strings formatted as 'Number-E-Number' as scientific notation if the text is passed through numeric evaluation functions like IsNumeric(). If you need to treat them strictly as invalid text, a custom Regex function via VBA is required.

Can I use 'Is Null' on a numeric data type field?

Yes, 'Is Null' checks if a field contains absolutely no data. It works on both Text and Number data types, as long as the numerical field hasn't defaulted to zero (0).

Office Quick Solutions