Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    29

    Unanswered: "This expression is typed incorrectly..."

    Here is my SQL statement:

    SELECT ProductList.ProductID
    ,ProductList.Score AS Expr1
    , ProductList.Ranking
    , [Company Info].Company
    , ProductList.Product
    , ProductList.Purpose
    , ProductList.Target
    , ProductList.Units
    , ProductList.Employment
    , ProductList.[Special Notes]
    , ProductList.[Select]

    FROM [Company Info] INNER JOIN ProductList ON [Company Info].Company = ProductList.Company

    WHERE (((ProductList.Score)<=([Forms]![ClientInfo]![Score]))
    AND ((ProductList.Ranking)>=([Forms]![ClientInfo]![Ranking]))
    AND ((([ProductList].[Purpose])=[Forms]![ClientInfo]![Purpose] Or [Forms]![ClientInfo]![Purpose] Is Null)<>False)
    AND ((([ProductList].[Target])=[Forms]![ClientInfo]![Target] Or [Forms]![ClientInfo]![Target] Is Null)<>False)
    AND ((([ProductList].[Units])=[Forms]![ClientInfo]![Units] Or [Forms]![ClientInfo]![Units] Is Null)<>False)
    AND ((([ProductList].[Employment])=[Forms]![ClientInfo]![Employment] Or [Forms]![ClientInfo]![Employment] Is Null)<>False))

    OR (((ProductList.Ranking)>=([Forms]![ClientInfo]![Ranking]))
    AND ((([ProductList].[Purpose])=[Forms]![ClientInfo]![Purpose] Or [Forms]![ClientInfo]![Purpose] Is Null)<>False)
    AND ((([ProductList].[Target])=[Forms]![ClientInfo]![Target] Or [Forms]![ClientInfo]![Target] Is Null)<>False)
    AND ((([ProductList].[Units])=[Forms]![ClientInfo]![Units] Or [Forms]![ClientInfo]![Units] Is Null)<>False)
    AND ((([ProductList].[Employment])=[Forms]![ClientInfo]![Employment] Or [Forms]![ClientInfo]![Employment] Is Null)<>False)
    AND (([Forms]![ClientInfo]![Score]) Is Null))

    OR (((ProductList.Score)<=([Forms]![ClientInfo]![Score]))
    AND ((([ProductList].[Purpose])=[Forms]![ClientInfo]![Purpose] Or [Forms]![ClientInfo]![Purpose] Is Null)<>False)
    AND ((([ProductList].[Target])=[Forms]![ClientInfo]![Target] Or Forms]![ClientInfo]![Target] Is Null)<>False)
    AND ((([ProductList].[Units])=[Forms]![ClientInfo]![Units] Or [Forms]![ClientInfo]![Units] Is Null)<>False) AND ((([ProductList].[Employment])=[Forms]![ClientInfo]![Employment] Or [Forms]![ClientInfo]![Employment] Is Null)<>False)
    AND (([Forms]![ClientInfo]![Ranking]) Is Null))

    OR (((([ProductList].[Purpose])=[Forms]![ClientInfo]![Purpose] Or [Forms]![ClientInfo]![Purpose] Is Null)<>False) AND ((([ProductList].[Target])=[Forms]![ClientInfo]![Target] Or [Forms]![ClientInfo]![Target] Is Null)<>False)
    AND ((([ProductList].[Units])=[Forms]![ClientInfo]![Units] Or [Forms]![ClientInfo]![Units] Is Null)<>False)
    AND ((([ProductList].[Employment])=[Forms]![ClientInfo]![Employment] Or [Forms]![ClientInfo]![Employment] Is Null)<>False)
    AND (([Forms]![ClientInfo]![Score]) Is Null)
    AND (([Forms]![ClientInfo]![Ranking]) Is Null))

    (SELECT Max(Score) From ProductList WHERE Score<=(ProductList.Score) GROUP BY ProductList.Product)

    ORDER BY ProductList.Score DESC , ProductList.Ranking DESC , [Company Info].Company;

    Without the subquery, this performs perfectly. However, there are multiple records of the same product differing only in Score. I'm trying to use the subquery to only give the records that have a Score closest to the value entered on the form. When I try to run this query as it reads, I get "This expression is typed incorrectly, or it is too complex to be evaluated." Can anybody tell me how to fix this?

  2. #2
    Join Date
    Feb 2009
    Posts
    29

    Help!!!

    Sorry, I probably should have mentioned that each query works when run separately. The error only occurs when I insert the subquery. Where is the error? I've been trying anything I can think of, with no luck. So I would really appreciate any advice anyone can give me.

    Thanks!!

Posting Permissions

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