Ms Access 2007 Error 3071
(עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) Home20132010Other VersionsLibraryForumsGallery Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Access 2007 Error 3071 -report using parameters Microsoft Office > Office 2010 - Planning, Deployment, and Compatibility Question 0 Sign in to vote I've created a report based on a query which uses parameters for start & end date. When I run the report which includes dates 24/6/11 to 30/6/11 (dd/mm/yy format) I get the following message appears: 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) Possible causes: You typed an expression that has invalid syntax. For example, an operand or operator may be missing, you may have typed an invalid character or comma, or you may have typed text without enclosing it with quotation marks ( " ). Check the expression to make sure you typed it correctly. You typed an expression that is too complex. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning some parts to variables. © 2010 Microsoft Corporation. All rights reserved. I have looked at all the expressions and cannot find any errors. I've tried other dates & it works fine. It works ok when I just run the query alone but not when I run the report. Monday, July 04, 2011 12:25 PM Reply | Quote Answers 0 Sign in to vote Hi Jane, Based on my research, you will receive this error messagewhen there are errors during the calculating, e.g.,you are dividing by another field and at times that field at a value of 0, and that will be the reasoncause the problem you were experiencing. To resolve this problem, go to check the calculate fields, modify the error you could find. e.g. checked if the denominator of the calculated field was not 0 before doing your calculatoins. Sample: field1: iif([fielda] = 0, 0, ( / Fielda)) Any questions, feel free to post back.Sincerely, Max Meng Forum Support Come back and mark the replies as answers if they help and unmark them if they provide no help. Marked as answer by Max MengMicrosoft contingent staff,
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only https://social.technet.microsoft.com/Forums/office/en-US/50321800-0a16-4cc8-b582-023bcea8376d/access-2007-error-3071-report-using-parameters?forum=officesetupdeployprevious takes a minute: Sign up (Error 3071) Access up vote 0 down vote favorite I'm trying to search for the Course ID by checking the the faculty ID and Course ID in my Course table. Its a simple query but when launched I receive a (This expression is typed incorrectly, or it is too complex to be evaluated. For example, a http://stackoverflow.com/questions/5959016/error-3071-access numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071) The VB script I'm using looks like this. Private Sub Course_ID_DblClick(Cancel As Integer) Dim x As Integer Dim y As String Dim z As Integer x = Me.Faculty_ID.Value Me.Course_ID.RowSource = "SELECT Course.[Course ID] FROM Course WHERE Course.[Course Name]=['Course']AND Course.[Faculty ID]='" & x & "'" End Sub ms-access access-vba share|improve this question edited May 11 '11 at 4:05 Petr Abdulin 14.9k63772 asked May 11 '11 at 3:49 Peter Trofimuk 11 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote I think you are getting this message because you are using wrong delimiters and confusing terminology in object names (where field content 'course' and table name [course] are the same. I guess your datasource is an ms-access database. You should then use the " (double quote) as value separator (you'll have to double the sign inside the expression) for text, and nothing for numbers. your select instruction could then look like: "SELECT Course.[Course ID] FR
first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register http://www.dbforums.com/showthread.php?1631874-Error-3071-When-Running-a-Query link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 7 of 7 Thread: Error 3071 When Running a Query Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 07-03-08,14:21 #1 sqluzr View ms access Profile View Forum Posts Registered User 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; ms access 2007 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. Reply With Quote 07-03-08,14:56 #2 rogue View Profile View Forum Posts Registered User 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? Reply With Quote 07-03-08,15:06 #3 sqluzr View Profile View Forum Posts Registe