Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Query Anomoly (2007 SP1)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Anomoly (2007 SP1)

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

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query Anomoly (2007 SP1)

    I got a bad feeling. Did you actually name a field "True/False"?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Query Anomoly (2007 SP1)

    Hi Mark

    No, the Field is called 'Active'.

    Hans, the Imported, Zipped DB is attached.

    Thanks heaps.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Anomoly (2007 SP1)

    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?

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Query Anomoly (2007 SP1)

    Sorry Hans. I forgot. Converted now.

    Thanks.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Anomoly (2007 SP1)

    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.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Query Anomoly (2007 SP1)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Anomoly (2007 SP1)

    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.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Query Anomoly (2007 SP1)

    Noted. I just hope I remember that, LOL!

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •