Filter returns different number of records for the same criteria

Question

Probably a bug in Access, but wanted to make sure:

I noticed the number of records returned from a query that filters out records where Field_Text_A = "Total" and the number of records returned when using a filter of the same source table was out by 1. The filter of the table was 1 higher.

I tried both filters  1) 'Does Not Contain "Total"', and 2) 'Does Not Contain ....' and they resulted in different answers. The first is the filter from the main menu when you right click a value in the table, and the second one is in the sub-menu 'Text Filters' of the same main menu. Filter 1 returns one more the filter 2. I enter the parameter "Total" (without quotes ofcourse) for filter 2.

Imran

Answer 1

You sure that the query without a filter returns all of the rows in the table?

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 2

Yes, without filters, the query and the table without filters have the same number of rows. I did just notice that the row 'missing' has another field (lets call it Field_Text_A-1 because it is the preceding field) with value "Total". I am pretty sure this has something to do with it.

Again, without filters, the numbers are correct (1263). But with filter on Field_Text_A = "Total", the query returns one less (the missing I describe above). Same with filter 2 described above.

Imran

Answer 3

Afraid I have no suggestions: I never work directly with tables, and I never use filters (preferring to set my own conditions explicitly in queries), so I have no experience in this area.

Good luck!

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 4

Thank you for your time Doug.

Answer 5

The first is the filter from the main menu when you right click a value in the table, and the second one is in the sub-menu 'Text Filters' of the same main menu.

The behaviour does differ.  The first will return rows with Null at the column position, the second will not.  The latter is strictly speaking more correct as a comparative expression involving Null should evaluate to Null, nether TRUE nor FALSE.  In a query for instance you would include Nulls in the result set by putting OR <ColumnName> IS NULL in the WHERE clause.

---

Ken Sheridan, Stafford, England

Answer 6

I did just notice that the row 'missing' has another field (lets call it Field_Text_A-1 because it is the preceding field) with value "Total". I am pretty sure this has something to do with it.

All records (Rows) would have the same fields so why do you mention the one records has an additional field? Do some records have that field as null?

Answer 7

Bulls-eye Ken!

The value for Field_Text_A for the record in question is indeed Null!

Thanks!

Imran