Struggling with trying to Exclude an ID number from a query

Question

I know this is probably a simple issue, but I've been struggling with this for some time . . .

I have a query where I need to exclude an ID# (Number Field) . . . and I've tried <>5600, Not 5600, Not = 5600 in the criteria field . . . it displays no records . . . when I have no criteria, all records display fine . . .when criteria is 5600, it properly displays records with ID# = 5600

There may or may not be records with 5600 in the query at certain times. . . does that matter?

Any help would be appreciated. . .

Answer 1

The condition <>5600 in the Criteria line should return all records for which ID# does have a value, but it's different from 5600. It will not include records for which ID# is null (blank). To include those, use

<>5600 Or Is Null

If you can't get it to work, could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then make the copy available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Post a link to the uploaded and shared file in a reply here.

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 2

Obviously, if there are no records that have the value of 5600 in the ID# field, then they will not be displayed. If there are records with 5600 but you exclude them with <> 5600 or Not 5600 in the criteria box, then they won't display either. So it appears your query works as designed.

By the way, never use the # symbol as part of a field name. It has reserved meaning in ACCESS and can cause problems if you use it as part of a table field name.

Answer 3

That worked . . .thanks . . . didn't realize I needed to deal with Null fields

Answer 4

The actual field name is BuyerID . . . but point well taken. . .

The issue (solved above) was that I wasn't dealing with null values in the field . . . it works now . . thanks for your feedback