Results 1 to 3 of 3
  1. #1
    drewetzel is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Location
    Austin, TX
    Posts
    34

    "expression is typed incorrectly, or is too complex to be evaluated" when sorting

    Hi,



    I have a series of queries set up to generate a sales report. When I run the top level query in the set I get the error in the Post title. I've used this query set for a long time with no issues, but yesterday I changed the column that the final data output is sorted on and that's when I started getting that error. Here is the SQL for the top level query. If anyone can help I'd greatly appreciate it. If more info is needed I'll be happy to post

    Code:
    SELECT Date() AS [Last Update], [Qry_Sales Report_Final pre].[Agent Name], IIf([Total Points]=0,9999,IIf([Total Points]<1,[cost],Round(nz([cost]/nz([Total Points],0),0),2))) AS CPS, [Qry_Sales Report_Final pre].[Total Points], [Qry_Sales Report_Final pre].Sold, [Qry_Sales Report_Final pre].Inbound, [Qry_Sales Report_Final pre].Outbound, [Qry_Sales Report_Final pre].[SV Close%], [Qry_Sales Report_Final pre].DISH, [Qry_Sales Report_Final pre].[DISH %], [Qry_Sales Report_Final pre].DHA, [Qry_Sales Report_Final pre].[DHA% (10%)], [Qry_Sales Report_Final pre].Flex, [Qry_Sales Report_Final pre].[Flex% (3%)], [Qry_Sales Report_Final pre].CCA, [Qry_Sales Report_Final pre].[CCA % (95%)], [Qry_Sales Report_Final pre].DVR, [Qry_Sales Report_Final pre].[DVR % (85%)], [Qry_Sales Report_Final pre].DTV, [Qry_Sales Report_Final pre].[DTV % (1%)], [Qry_Sales Report_Final pre].DSL, [Qry_Sales Report_Final pre].[DSL % (4%)], [Qry_Sales Report_Final pre].Cable, [Qry_Sales Report_Final pre].[Cable % (5%)], [Qry_Sales Report_Final pre].HN, [Qry_Sales Report_Final pre].[HN % - (1&1/2%)], [Qry_Sales Report_Final pre].WB, [Qry_Sales Report_Final pre].[WB % - (1%)], [Qry_Sales Report_Final pre].Qwest, [Qry_Sales Report_Final pre].[Qwest % (1%)], [Qry_Sales Report_Final pre].EL, [Qry_Sales Report_Final pre].[EL%], [Qry_Sales Report_Final pre].Cyb, [Qry_Sales Report_Final pre].[Cyb %], [Qry_Sales Report_Final pre].Charter, [Qry_Sales Report_Final pre].[Charter %], [Qry_Sales Report_Final pre].[PF to Collect], [Qry_Sales Report_Final pre].[PF Collected], [Qry_Sales Report_Final pre].[Collected %], [Qry_Sales Report_Final pre].[PF Waived], [Qry_Sales Report_Final pre].[Waived %], [Qry_Sales Report_Final pre].[Alt Phone], [Qry_Sales Report_Final pre].[Alt Phone % (73%)], [Qry_Sales Report_Final pre].[E-mail], [Qry_Sales Report_Final pre].[E-mail % (95%)]
    FROM [Qry_Sales Report_Final pre]
    ORDER BY [Qry_Sales Report_Final pre].[SV Close%] DESC;

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The query looks OK.

    I think it might have something to do with the field names. Names in Access should only be letters, numbers and/or the underscore. NO spaces, NO special characters. You have percent sign, minus sign and parenthesis , sometimes all of them in the same name. IMO, this needs to be fixed first.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Since the names are all enclosed in [], they should not be issue. It is a good idea to avoid those naming structures. Also, some special characters (#, *, ', ", ?) in data can be an issue in VBA code.

    Issue might be with the Nz that returns a 0 in divisor. Can't divide by 0. Change to 1.

    SELECT *, Date() As [Last Update], IIf([Total Points]=0,9999,IIf([Total Points]<1,[cost],Round(nz([cost]/nz([Total Points],1),0),2)))
    AS CPS
    FROM [Qry_Sales Report_Final pre]
    ORDER BY [Qry_Sales Report_Final pre].[SV Close%] DESC;

    Does [Qry_Sales Report_Final pre] run without error?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-24-2012, 12:34 PM
  2. Replies: 1
    Last Post: 04-25-2012, 10:36 AM
  3. This expression is typed incorrectly
    By Ray67 in forum Queries
    Replies: 9
    Last Post: 01-03-2012, 12:53 PM
  4. Replies: 3
    Last Post: 12-30-2011, 12:43 PM
  5. Replies: 3
    Last Post: 03-31-2011, 11:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums