"This expression is typed incorrectly, or it is too complex to be evaluated."

[LanieB]

Hello all! I can only hope someone can figure out what happened with my query It worked fine when I made the databased and tested it out... and today it has decided to quit. I HAVE NO IDEA WHY!

Here is my query.

SELECT AirExport.Division, AirExport.[Audit Season/Year], Avg([FF_BookingSheet_AE]/[FF_BookingSheet_AA]) AS [File Folder Booking Sheet], Avg([FF_Organization_AE]/[FF_Organization_AA]) AS [File Folder Organization], Avg([DO_FileCopy_AE]/[DO_FileCopy_AA]) AS [File copy of all PHX invoices/credits issued], Avg([DO_Quotation_AE]/[DO_Quotation_AA]) AS [Quotation with back-up or CCP], Avg([DO_AES_AE]/[DO_AES_AA]) AS [AES Summary Report], Avg([DO_HAWB_AE]/[DO_HAWB_AA]) AS HAWB, Avg([DO_PHXdoc_AE]/[DO_PHXdoc_AA]) AS [PHX issued docs], Avg([DO_HazDec_AE]/[DO_HazDec_AA]) AS [Haz Dec], Avg([DO_SLI_AE]/[DO_SLI_AA]) AS [Shippers letter of instruction], Avg([DO_HazCheck_AE]/[DO_HazCheck_AA]) AS [Haz Mat check list], Avg([DO_BOL_AE]/[DO_BOL_AA]) AS [Inland bill of lading], Avg([DO_PickUp_AE]/[DO_PickUp_AA]) AS [Pick up records], Avg([DO_LC_AE]/[DO_LC_AA]) AS [Copy of L/C and copy of all docs to bank], Avg([CP_POA_AE]/[CP_POA_AA]) AS [POA field updated], Avg([CP_Consent_AE]/[CP_Consent_AA]) AS [Consent to Screen indicated], Avg([CP_Known_AE]/[CP_Known_AA]) AS [Known/Unknwn shipper field], Avg([CP_Contact_AE]/[CP_Contact_AA]) AS [Contact name/phone/fax-email], Avg([CP_OD_AE]/[CP_OD_AA]) AS [O/D and type of service], Avg([CP_Rates_AE]/[CP_Rates_AA]) AS [Sell rates statement], Avg([CP_SellRate_AE]/[CP_SellRate_AA]) AS [Sell Rate], Avg([CP_Fee_AE]/[CP_Fee_AA]) AS [Hdg/Forwarding fee], Avg([CP_Dray_AE]/[CP_Dray_AA]) AS [Inland/Dray sell rate], Avg([CP_Other_AE]/[CP_Other_AA]) AS [Other appl fees], Avg([CP_Special_AE]/[CP_Special_AA]) AS [Special instructions], Avg([CP_Assay_AE]/[CP_Assay_AA]) AS [Assy account], Avg([CP_Routing_AE]/[CP_Routing_AA]) AS [Routing information], Avg([JC_Remarks_AE]/[JC_Remarks_AA]) AS [J/C Remarks], Avg([JC_Memo_AE]/[JC_Memo_AA]) AS [Memos used properly], Avg([TSA_Acceptance_AE]/[TSA_Acceptance_AA]) AS Acceptance, Avg([TSA_Consent_AE]/[TSA_Consent_AA]) AS Consent, Avg([TSA_KSMS_AE]/[TSA_KSMS_AA]) AS KSMS, Avg([TSA_SSE_AE]/[TSA_SSE_AA]) AS SSE, Avg([SP_QuoteNumber_AE]/[SP_QuoteNumber_AA]) AS [Freight quote number], Avg([SP_PO_AE]/[SP_PO_AA]) AS [PO # field correct], Avg([SP_Invoice_AE]/[SP_Invoice_AA]) AS [Invoice # field correct], Avg([SP_Shipper_AE]/[SP_Shipper_AA]) AS [Shipper match docs], Avg([SP_Cnee_AE]/[SP_Cnee_AA]) AS [Cnee match docs], Avg([SP_Nature_AE]/[SP_Nature_AA]) AS [Nature/ Qty goods accurate], Avg([SP_PC_AE]/[SP_PC_AA]) AS [Pc descr as OH or PL], Avg([SP_Type_AE]/[SP_Type_AA]) AS [Type of Service], Avg([SP_Declared_AE]/[SP_Declared_AA]) AS [Declared Value], Avg([SP_DD_AE]/[SP_DD_AA]) AS [DDU/DDP indicators correct], Avg([SP_CCCode_AE]/[SP_CCCode_AA]) AS [Consol Cost chg code correct], Avg([SP_CCData_AE]/[SP_CCData_AA]) AS [Consol Cost data accurate], Avg([SP_Billing_AE]/[SP_Billing_AA]) AS [Billing w/in 2 days export], Avg([SP_ChgCode_AE]/[SP_ChgCode_AA]) AS [Chg codes correct for all], Avg([SP_Memos_AE]/[SP_Memos_AA]) AS [Memos created for all exp], Avg([SP_Rates_AE]/[SP_Rates_AA]) AS [Rates billed correct], Avg([AES_POA_AE]/[AES_POA_AA]) AS [Valid POA on file], Avg([AES_Time_AE]/[AES_Time_AA]) AS [On Time], Avg([AES_USPPI_AE]/[AES_USPPI_AA]) AS USPPI, Avg([AES_UCnee_AE]/[AES_UCnee_AA]) AS [Ultimate Cnee], Avg([AES_ICnee_AE]/[AES_ICnee_AA]) AS [Intermediate Cnee], Avg([AES_EIN_AE]/[AES_EIN_AA]) AS [USPPI EIN], Avg([AES_Origin_AE]/[AES_Origin_AA]) AS [State of Origin], Avg([AES_Related_AE]/[AES_Related_AA]) AS [Related pty indicator], Avg([AES_Routed_AE]/[AES_Routed_AA]) AS [Routed indicator], Avg([AES_Export_AE]/[AES_Export_AA]) AS [Port of export], Avg([AES_Mode_AE]/[AES_Mode_AA]) AS Mode, Avg([AES_Carrier_AE]/[AES_Carrier_AA]) AS Carrier, Avg([AES_Date_AE]/[AES_Date_AA]) AS [Date of export], Avg([AES_Destination_AE]/[AES_Destination_AA]) AS [Destination country], Avg([AES_B_AE]/[AES_B_AA]) AS [Schedule B], Avg([AES_Weight_AE]/[AES_Weight_AA]) AS [Gross Weight], Avg([AES_License_AE]/[AES_License_AA]) AS [License Auth], Avg([AES_ECCN_AE]/[AES_ECCN_AA]) AS ECCN, Avg([AES_Stat_AE]/[AES_Stat_AA]) AS [Stat Qty], Avg([AES_DF_AE]/[AES_DF_AA]) AS [D/F indicator], Avg([AES_Value_AE]/[AES_Value_AA]) AS [Value], Avg([AES_Haz_AE]/[AES_Haz_AA]) AS [Hazardous Freight indicator], Avg([AES_Verify_AE]/[AES_Verify_AA]) AS [Verify Msg], Avg([AES_Fatal_AE]/[AES_Fatal_AA]) AS [Fatal error - same day], Avg([AES_Alert_AE]/[AES_Alert_AA]) AS [Compliance alert], Avg(AX_Points.Average) AS AvgOfAverage
FROM AirExport, AX_Points
GROUP BY AirExport.Division, AirExport.[Audit Season/Year]
HAVING (((AirExport.Division)="01") AND ((AirExport.[Audit Season/Year])=[Audit Session])) OR (((AirExport.Division)="1"));


Here is the error I get...


"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)"

I'm stumped

[aytee111]

Delete one of the items, run the query, delete the next one, run the query...etc

This will highlight for you exactly which expression is causing the error.

[LanieB]

I noticed if I remove all the data it runs fine, but once that data is in the table I get this error. I entered in like 30 mock entries in the beginning and had no problems and now we only have 5 records in there and I get this. I tried to remove them one at a time (the records), but that didn't narrow it down. I entered in two mock ones into the database and it all worked fine..
I'm perplexed

[aytee111]

Not the records, remove the expressions in the query!

It may be that you have null values on the table and are trying to do an arithmetic function against it?

This SQL string is too complex for ME to evaluate!