Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Query to complex error: help

    I posted this before.. But I didn't quite understand the response.. So here I go again..

    I am getting an:

    Error:

    The expression is typed incorrectly, or it is too complex to be evaulated. For example, a numeric expression may contain...

    This is the sql for my query:

    Code:
    PARAMETERS [Forms]![frmOnPlantReport]![txtStartDate] DateTime, [Forms]![frmOnPlantReport]![txtEndDate] DateTime, [Forms]![frmOnPlantReport]![txtSite] Text;
    SELECT [LT Shipment Info].Material, Area1([LT Shipment Info].[Material]) AS AREA, [LT Shipment Info].GenName, [LT Shipment Info].GenAlias, [LT Shipment Info].RecName, [LT Shipment Info].RecAlias, [LT Shipment Info].DisposalMethod, [LT Shipment Info].ManifestNum, [LT Shipment Info].AmountLbs, [LT Shipment Info].[Ship Date], [LT Shipment Info].RcvdDate, [LT Shipment Info].ManAccDate, [LT Shipment Info].NumContainers, [LT Shipment Info].ChargeCodeOff, [LT Shipment Info].ChargeCodeOn, [LT Shipment Info].TNRCC, [LT Shipment Info].DisposalTax, [LT Shipment Info].[Incoming/Outgoing], [LT Waste Information].[Primary Receiver Cost Per Pound], IIf([OtherCost]>0,[OtherCost],0) AS HandleCost
    FROM [LT Shipment Info] INNER JOIN [LT Waste Information] ON [LT Shipment Info].Material = [LT Waste Information].Material
    WHERE ((([LT Shipment Info].Material) Not Like 'SR-I-*') AND ((Area1([LT Shipment Info].[Material]))=[Forms]![frmOnPlantReport]![txtSite]) AND (([LT Shipment Info].GenAlias)='SR') AND (([LT Shipment Info].RecAlias)='SR') AND (([LT Shipment Info].DisposalMethod) Like 'Inc*') AND (([LT Shipment Info].RcvdDate) Between [Forms]![frmOnPlantReport]![txtStartDate] And [Forms]![frmOnPlantReport]![txtEndDate])) OR ((([LT Shipment Info].GenAlias)='SRI') AND (([LT Shipment Info].RecAlias)='SR') AND (([LT Shipment Info].DisposalMethod) Like 'Inc*'));

    If you need to see anything else .. let me know..

    Thanks for your help...

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And what do you want help on??? Or more bluntly: what do you want?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    why i'm getting this error.. and how to fix it

    thanks

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by TonyT
    why i'm getting this error.. and how to fix it

    thanks
    Why? QBE can't handle it ... How to fix? Make it more simple -or- run it using DAO or ADO ...

    Something to keep in mind: QBE can handle ordinary queries. It can't handle really complex ones (or long ones with many many columns) or those that are not supported by the Jet driver ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2004
    Posts
    161
    Can be the length of the string, the value of fields or the Area1 function...
    Try to simplify to test each part of your query

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    Ok I did as you suggested.. and tested each part of my query.. and the problem points to my function..

    Field: Area1([LT Shipment Info].[Material]))
    Criteria: [Forms]![frmOnPlantBillingReport]!txtSite]

    If I take out the criteria then the form runs.. if i add it back it i get the error..

    AREA1 is in a module "modArea"

    Code:
    Function Area1(MATERIAL As String) As String
    
    ' \\\\\\ Defines the site name for On-Site Billing Invoice. ///////
    
    Select Case True
        Case MATERIAL Like "SR-A-*"
            Area1 = "ADIPURE"
        Case MATERIAL Like "SR-C*EC*"
            Area1 = "C-UNIT"
        Case MATERIAL Like "SR-D*EC*"
            Area1 = "D-UNIT"
        Case MATERIAL Like "SR-E-*"
            Area1 = "DIAMINE"
        Case MATERIAL Like "SR-EC*"
            Area1 = "P&IP"
        Case MATERIAL Like "SR-ES-*"
            Area1 = "ENVIRONMENTAL SERVICES"
        Case MATERIAL Like "SR-G*EC*"
            Area1 = "G-UNIT"
        Case MATERIAL Like "SR-G-*"
            Area1 = "POWER"
        Case MATERIAL Like "SR-I-*"
            Area1 = "ENVIRONMENTAL SERVICES"
        Case MATERIAL Like "SR-IL*"
            Area1 = "INTERMEDIATES LAB"
        Case MATERIAL Like "SR-X-*"
            Area1 = "ENVIRONMENTAL SERVICES"
        Case MATERIAL Like "SR-K-*"
            Area1 = "ETHYLENE"
        Case MATERIAL Like "SR-N-*"
            Area1 = "CSD"
        Case MATERIAL Like "SR-P-*"
            Area1 = "ADN"
        Case MATERIAL Like "SR-Q-*A"
            Area1 = "SITE SERVICES"
        Case MATERIAL Like "SR-Q-*"
            Area1 = "SITE SERVICES"
        Case MATERIAL Like "SR-RL-*"
            Area1 = "RESEARCH LAB"
        Case MATERIAL Like "SR-TW-*"
            Area1 = "ENVIRONMENTAL SERVICES"
        Case MATERIAL Like "SR-PL-*"
            Area1 = "PROCESS LAB"
        Case MATERIAL Like "SR-CG-*"
            Area1 = "COGEN"
        Case Else
            Area1 = MATERIAL
    End Select
    End Function
    I would I simplfy that to make it work?

    Thanks

  7. #7
    Join Date
    Jul 2003
    Posts
    292
    Found the problem..

    I had a null value in my material field..

    thanks 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
  •