Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Kingwood, Texas, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unpredictable SQL results (Access 97)

    I'm working on a database that generates resumes from Word and am using a form with nine items the user can choose to query simultaneously. Although Access does generate these queries, query results are unpredictable when more than 4 items are checked, in that Access sometimes throws in a result that shouldn't be returned. Am I running into some kind of size limit (as in queries can only be a certain length)?

    Here's an example of an actual query that returned someone who had nothing entered under tblEmployee.Discipline and "Master's" entered under tblEmployee.HighestDegree:
    SELECT DISTINCT tblEmployee.FirstName, tblEmployee.LastName
    FROM (((((tblEmployee INNER JOIN tblMarketSectors ON tblEmployee.EmployeeID = tblMarketSectors.EmployeeID) INNER JOIN tblSiteExp ON tblEmployee.EmployeeID = tblSiteExp.EmployeeID) INNER JOIN tblProjects ON tblEmployee.EmployeeID = tblProjects.EmployeeID) INNER JOIN tblLanguages ON tblEmployee.EmployeeID = tblLanguages.EmployeeID) INNER JOIN tblQualifications ON tblEmployee.EmployeeID = tblQualifications.EmployeeID) INNER JOIN tblSeniorRoles ON tblEmployee.EmployeeID = tblSeniorRoles.EmployeeID
    WHERE tblMarketSectors.MktSector Like '*lng*' OR tblProjects.[Project Type] Like '*lng*' OR tblProjects.ProjectPara Like '*lng*' AND tblSiteExp.Site LIKE '*nigeria*' AND tblProjects.Client LIKE '*nigeria*' AND tblProjects.Country = "Nigeria" AND tblEmployee.Discipline LIKE '*test*' AND tblLanguages.Language LIKE '*french*' AND tblEmployee.HighestDegree = "Bachelor's" AND tblQualifications.Qualification LIKE '*chemical*';

    Results returned are correct when individual search items are selected, and so far as I have been able to test, they are correct when any combination of up to four search items are used. After that, it sometimes messes up when five to eight items are selected and it always messes up when all nine are selected. (By the way, in our resumes "Nigeria" does appear as part of client, site, and country field entries. Also, site means the employee actually worked in that country, while country means the employee's projects were located in that country.)

    Any ideas?

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

    Re: Unpredictable SQL results (Access 97)

    What you first might try is to use an alias for the 2 tables, that will make the SQL a little easier to read and may help if length is a problem.
    For example:

    old: tblEmployee.LastName FROM (((((tblEmployee INNER JOIN

    Change to: E.LastName FROM (((((tblEmployee AS E INNER JOIN

    I also had a hard time following all the OR's and AND's in your WHERE statement; and perhaps Access interprets it differently than you expect. I'd using parenthesis around the clauses to make it easier to read and avoid any possible confusion.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Kingwood, Texas, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unpredictable SQL results (Access 97)

    You're right about the ORs and ANDs. Right after I posted this, I mentioned this problem to my boss & he called a programmer friend of his who told me to put parentheses around all the ORs, which I did as follows:

    strWhere = strWhere & " AND " + "(tblMarketSectors.MktSector Like '*" + txtMarketSector + "*' OR tblProjects.[Project Type] Like '*" + txtMarketSector + "*' OR tblProjects.ProjectPara Like '*" + txtMarketSector + "*')"

    (The initial AND gets stripped off later.)

    This seems to have fixed it, as these were the only ORs. So far as I can tell with only a little testing, I can now run reliable queries on all 9 items simultaneously.

    Thank you for your help!

Posting Permissions

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