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

    Unanswered: Complex query error.. help

    I have a query that is giving me the following error..

    The expression is typed incorrectly, or it is too complex to be evaulated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

    I been looking back and forth.. I do not see whats wrong..

    heres the sql

    Code:
    PARAMETERS [Forms]![frmOnPlantReport]![txtSite] Text, [Forms]![frmOnPlantReport]![txtStartDate] DateTime, [Forms]![frmOnPlantReport]![txtEndDate] DateTime;
    SELECT [LT Shipment Info].Material, Area([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 ((Area([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*'));
    Thanks for your help

  2. #2
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Code:
    PARAMETERS [Forms]![frmOnPlantReport]![txtSite] Text, [Forms]![frmOnPlantReport]![txtStartDate] DateTime, [Forms]![frmOnPlantReport]![txtEndDate] DateTime;
    I think here's your problem. I am kind of new to SQL but never seen a like this ...if you are using information that transfers data from a txtbox to like filter data, you don't put them like that. All you do is do it in WHERE statements...
    Ryan
    My Blog

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    hmmm.. this has worked before.. The only thing that I have changed is..

    under the select selction of the sql..

    Area([LT Shipment Info].[Material]) AS AREA

    I'm pulling a funtion module here to do some case stmts.

    And now I using this Area field in my query to pull my information..

    Would that make it complexed? if so I can i fixed this ?

    Thanks for your response.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by TonyT
    hmmm.. this has worked before.. The only thing that I have changed is..

    under the select selction of the sql..

    Area([LT Shipment Info].[Material]) AS AREA

    I'm pulling a funtion module here to do some case stmts.

    And now I using this Area field in my query to pull my information..

    Would that make it complexed? if so I can i fixed this ?

    Thanks for your response.
    Look at your response carefully ... Clue: Case-insensative. You can't call a function AND name a column the same ....
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    M Owen,

    I'm not sure I understand what you are saying here..

    I changed :

    AREA: Area1([LT Shipment Info].[Material])

    my SQL looks like so:

    Code:
    PARAMETERS [Forms]![frmOnPlantReport]![txtSite] Text, [Forms]![frmOnPlantReport]![txtStartDate] DateTime, [Forms]![frmOnPlantReport]![txtEndDate] DateTime;
    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*'));
    Thanks for your time..

Posting Permissions

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