Microsoft access: query with Null

Question

Hi, I am creating a Microsoft access query. I wish to filter 2 columns.

1st column: I want it to return rows that are null. I have indicated “=is null” under the criteria section but my entire data sheet page returns empty even though there are rows that are null for 1st column.

2nd column: I want it to return rows that only consist of numbers as this field contains both numbers and alphabets.

What would be the criteria/formula I can input for both columns in my query design?

Thanks in advance!

Answer 1

1. Use Is Null, not "=Is Null", as criterium for the first field.
2. Add a calculated field IsNumeric([YourMixedField]) having as criterium: True

Answer 2

Hi GustavBrock,

Thanks so much for your prompt response! Regarding #2: my calculated field is

Revised PO:IsNumeric([Customer PO)]

My criteria is: true.

However when I run the data sheet view, that column displays “-1” for all rows.

Answer 3

And what are those values not supposed to be numeric?

It can read value like "2E10" as numeric ...

Answer 4

as this field contains both numbers and alphabets.？

not like *[a-zA-Z]*

Answer 5

Hi Gustav,

You’re right. Yes some fields contain items like 2lash123. But I want fields that solely contain numbers.

Thank you.