Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    9

    Unanswered: Error 3071 When Running a Query

    Hello All,
    I have three queries:

    qryACDCallsAns:

    SELECT DISTINCTROW DAgentGroupByAgent.AgentName, DAgentGroupByAgent.AgentId, Sum(DAgentGroupByAgent.AcdCallsAnswered) AS [Sum Of AcdCallsAnswered]
    FROM DAgentGroupByAgent
    WHERE (((DAgentGroupByAgent.EVENTDATE)>=[st_date] And (DAgentGroupByAgent.EVENTDATE)<[en_date]))
    GROUP BY DAgentGroupByAgent.AgentName, DAgentGroupByAgent.AgentId;

    qryCallAcctFor:
    SELECT DISTINCTROW tblCallRecord.AGENT, Count(tblCallRecord.AGENT) AS [Sum Of AGENT]
    FROM tblCallRecord
    WHERE (((tblCallRecord.DATETIME)>=[st_date2] And (tblCallRecord.DATETIME)<[en_date2]))
    GROUP BY tblCallRecord.AGENT;

    I am passing st_date and en_date as parameters to make the two queries dynamic. When I run both queries, I get the results that I want with no issues.

    The issue is when I run my third query to query the other 2 queries:

    SELECT [qryACDCallsAns].[AgentName], [qryCallsAcctFor].[Sum of Agent] AS [Calls Acctd For], [qryACDCallsAns].[Sum of ACDCallsAnswered] AS [ACD Calls Ansd]
    FROM qryACDCallsAns LEFT JOIN qryCallsAcctFor ON INT([qryACDCallsAns].AgentID) = INT([qryCallsAcctFor].Agent);

    It prompts me for 4 parameters which are st_date, en_date, st_date2 and en_date2 and then gives me the following error in MS Access:

    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)


    Any ideas would be greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Where are you running this? How are the variables being passed - where are they coming from?

  3. #3
    Join Date
    Jun 2008
    Posts
    9
    I am running this in MS Access. The variables are being passed when I double-click the query to run it, it then asks me to enter in the parameter values.

    After I enter the dates for the parameters, then the query takes those values and produces the results of the query. It works fine for the first two queries but gives me an error on the third query (which is basically a query that queries the other two queries).

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Use a form to enter the criteria and refer to the form control(s) in the query criteria.

  5. #5
    Join Date
    Jun 2008
    Posts
    9
    I am actually passing the date variables from an asp page. So, I don't think I will need a form in access.

    When I received an error on the asp page originally, I tried to test it in Access and discovered the underlying error. So, if I can get it to work in MS Access first, then I beleive it should work on my asp page.

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Make sure that all of your data types are correct and that Agent and Agent ID are the same data type.

  7. #7
    Join Date
    Jun 2008
    Posts
    9
    Thank you Rogue, I removed the int()'s from the following line:

    INT([qryACDCallsAns].AgentID) = INT([qryCallsAcctFor].Agent);

    They both were of the same data type to begin with. Now the query works fine.

    Thank you once again.

Posting Permissions

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