Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Unanswered: Combo does not work in query

    I have a table called students, field [classnumber] is lookup field from table CLASSES with field [classnumber]

    I tried to run a query with the classnumber field but if put a number in the criteria block I get nothing.

    So I took the students table, made classnumber a text field and typed the class number in manually; bam it queries with no problem.

    Q. Why can't I run a query from a field that is a lookup in the main table?

    Peter

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by PJHAction
    Q. Why can't I run a query from a field that is a lookup in the main table?
    You can. You just have to make sure you're querying on the correct field (ID or text field) (one reason I discourage lookups based on the table directly.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Table lookups are a scourge. Microsoft should NEVER have created this monstrosity. All it does is promote poor database design.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    If you discourage table lookups, what is your thought on a list box?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Combo Boxes and List Boxes are fine if they are on forms.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    Great! Thanks for the info, I will change my development process.

    Peter

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes - Definately remove any lookups based directly on the table (make separate lookup tables if not already). Then make the combobox/listbox on your form based on that separate table. As StarTrekker mentioned, it's a scourge and one of the worst "features" Microsoft could've added as it only confuses.
    Last edited by pkstormy; 06-12-08 at 16:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good man Peter
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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