Query Anomoly (2007 SP1)

[farscape_fan]

I have created a Table with just 3 Fields (ID, Number, and True/False).

I populated it with the numbers 1-5000, as users are going to select a number in this range from a Combo Box.

If the True/False Field is set to True, then the numbers shouldn't appear in the Combo Box, so I've created a Query that Selects these 3 Fields and have added 'False' to the T/F Criteria.

HOWEVER! . . . Access ignores the first 255 Records and only returns Records 256 through 5,000, even though there are just 3 numbers in the whole 5,000 with a True Condition.

Does anyone know why Access would ignore the first 255 Records?

I'm completely spun-out!

Thanks for any advice.

[HansV]

Can you import the table and query into a blank new database, zip it and attach the zip file to a reply?

[MarkLiquorman]

I got a bad feeling. Did you actually name a field "True/False"?

[farscape_fan]

Hi Mark

No, the Field is called 'Active'.

Hans, the Imported, Zipped DB is attached.

Thanks heaps.

[HansV]

Sorry, I can't open .accdb files since I don't have Access 2007. Can you convert it to an Access 2000 format database (.mdb) and zip that?

[farscape_fan]

Sorry Hans. I forgot. Converted now.

Thanks.

[HansV]

The "missing" records *are* returned by the query but they're listed at the end. If you specify that the query should be sorted by SRN, the "missing" records will be listed at the top.
If you make SRN the primary key of the table, you wouldn't have the problem.

Note: I don't see any need for the SRNID field - you can use SRN itself as unique identifier.

[farscape_fan]

How embarrassing! I'm so sorry for wasting your time.

I just thought that it would naturally sort it numerically, by ID, or simply by the order in which the Records existed. As all these *are* numbered in order, I thought it would have met all 3 of those criteria.

I do find it odd that Access just randomly begins with Record 256 though, but, hey, I'm just glad they're appearing.

So sorry again for wasting your time.

Thanks for taking a look. Greatly appreciated, as always.

Have a great weekend.

[HansV]

Records aren't stored in any particular order in a table, you should never assume that they will be sorted by something that seems logical to you.
But if you assign a primary key to the table, Access will always display the records sorted by the primary key unless you explicitly specify otherwise.
Having a primary key also makes queries based on the table more efficient.

[farscape_fan]

Noted. I just hope I remember that, LOL!

Thanks again.