Access Error - "expression Is Typed Incorrectly..."
Sep 24, 2007
I have a functioning query. However, I need to remove the "Term Date" field. When I do that, I receive a message that says "The 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." The SQL code is listed below. Any help with this problem is greatly appreciated!
SELECT dbo_v_PenLiab_401k_rpt.EMPLOYID AS EmpID,
QryEligibility.EligDate,
Sum(dbo_v_PenLiab_401k_rpt.[Gross Pay]) AS GrossPay,
IIf((Max([dbo_v_PenLiab_401k_rpt]![Check Date])<[QryEligibility]![EligDate]) Or [QryHrsElig]![EligHrs]=1,0,Sum(IIf([QryEligibility]![EligDate]<[dbo_v_PenLiab_401k_rpt]![Check Date],[dbo_v_PenLiab_401k_rpt]![Gross Pay],0))) AS AdjustedGrossPay,
Sum(dbo_v_PenLiab_401k_rpt.[401k Amt]) AS 401kAmt,
Sum([401k Amt])/Sum([Gross Pay]) AS EEDefPct,
IIf(([QryEligibility]![EligDate]>Max([dbo_v_PenLiab_401k_rpt]![Check Date])) Or [QryHrsElig]![EligHrs]=1,0,IIf([EEDefPct]>0.04,0.02,[EEDefPct]*0.5)) AS ErMatchPct
FROM (dbo_v_PenLiab_401k_rpt LEFT JOIN QryEligibility ON dbo_v_PenLiab_401k_rpt.EMPLOYID = QryEligibility.EMPLOYID) LEFT JOIN QryHrsElig ON dbo_v_PenLiab_401k_rpt.EMPLOYID = QryHrsElig.EmpID
WHERE (((dbo_v_PenLiab_401k_rpt.[Check Date])>=[Forms]![ReportDateRange]![Beginning Date] And (dbo_v_PenLiab_401k_rpt.[Check Date])<=[Forms]![ReportDateRange]![Ending Date]))
GROUP BY dbo_v_PenLiab_401k_rpt.EMPLOYID, QryEligibility.EligDate, QryHrsElig.EligHrs, dbo_v_PenLiab_401k_rpt.[Term Date]
HAVING (dbo_v_PenLiab_401k_rpt.[Term Date]=#1/1/1900#)
ORDER BY dbo_v_PenLiab_401k_rpt.EMPLOYID;
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."
I did a search and it looks like date prompts cause this error sometimes. The message pops up when running from the Switchboard to generate a report. When you hit the button, you are prompted for the Start Date and End Date, but then this message appears.
Running the query directly, not using the Switchboard or Report to generate the data, it runs fine, still using the two prompts.
Any idea on what I should look at to resolve this issue so users can run the report from the switchboard? Let me know if I need to post the sql or something.
I am getting this error "This expression is typed incorrectly, or is too complex to be evaluated"
For this query: SELECT first([TblProp].[Name]) AS [SName], First([TblProp].[CommentDate]) AS DateCommented, First([TblProp].[No]) AS BNum, First([TblProp].[Indication]) AS Ind, First([TblProp].[PropSubmitted]) AS DateSub, First([TblProp].[Contact]) AS PrimCon, First([TblProp].[Prepared]) AS PrepName, First([TblProp].[Comment]) AS Comment,First([TblProp].[Value]) AS ValueNew, First([TblProp].[Rating]) AS Prob FROM TblProp GROUP BY [TblProp].[RFPNo];
This query had been working fine for a good long time, but suddenly it starts throwing up this message. I haven't changed anything at all with this query. Its very puzzling. I went through each of the fields and its the comment field that is causing the problem.
Would anyone have any ideas on why this might be happening?
I am trying to create a expression to calculate the net fine of a book by the expression below by using a query:
Net Fine: IIf(DateDiff("d",[Issue Date],Date())>7 And [Returned]=True,(DateDiff("d",[Issue Date],Date())-7)*15,"0")
The returned field is a checkbox. The expression works but the record keeps updating and as a result the net fine increases everyday. So is there any way to keep the fine from increasing. I know then problem is occuring due to the usage of Date(). And I dont want use VB for this task. And is there any way to set its data type to currency.
I am trying to get some of my colleagues to open a database I have created. They keep getting an error message on some of my forms:
"The expression after update you entered as the event property setting produced the following error – can’t find project or library Expression may not result in the name of a macro, the name of a user defined function, or event procedure There may have been an error evaluating an event, function or macro."
What does this mean? I'm assuming it has osmething to do with the code I have in AfterUpdate events but these are only requerys for cascading combos. Example:
Private Sub cboBusiness_AfterUpdate() Me.cboBusinessUnit.Requery Me.cboBusinessUnit.SetFocus End Sub
Where do I need to look to sort this? It works fine on my PC, but nobody elses.
I created an expression in my query, when I run the report or try to go into it the design view I get the following error message: The expression you entered exceeds the 1024-character limit for the query design grid.
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
I need to implement the following logic in a report column:
IF hist_mis_cds!typgess = "", then calculated field= "" ELSE IF hist_mis_cds!typgess <> hist_code_translation! src_code_desc then calculated field= "X" ELSE calculated field= ""
I have implemented the following query in Expression Builder for the calculated field:
I have a query that gives me the "3071 - Expression too complex to be evaluated.." error. The problem is I can get the EXACT same SQL to run and not produce the error. The query is part of a reporting tool that hundreds of users run (each on their own instance or a few people sharing one instance). Here's the flow of what I do:1 - Open DB2 - run query3 - Query dies w/ "3071 - Expression too complex..." error4 - open query in SQL edit mode5 - Rerun query and it works fine.6 - Scratch head and curse loudly....After modifying the query it works for a random amount of time and users don't complain. Then all of the sudden, it starts failing again, and I repeat the query edit, resave and it works fine until the next time. The problem is I never know what may or may not fix it because it will work fine after opening in edit mode. I've tried compact/repair and that has no effect on the query. The only thing that fixes it is opening it in design mode.I need to figure out what is the root cause of this. I'm getting sick of fixing it with my edit/resave hack. It's not excessively complex or anything.Here's my SQL. It uses some form value references in the criteria, but the form is open and values exist when I'm trying to run the query, so that shouldn't be it.SELECT [VARIANCE DIVISION].MONTHNBR, CHOOSE([MONTHNBR],"January","February","March","April", "May","June","July","August","September", "October","November","December") AS MTHNAME, [VARIANCE DIVISION].INVTYPE, [VARIANCE DIVISION].TRFTYPE, [VARIANCE DIVISION].DIV_CODE, [VARIANCE DIVISION].DIV_DESC, [VARIANCE DIVISION].LED, [VARIANCE DIVISION].MATL, [VARIANCE DIVISION].SOURCEDIVCODE, [VARIANCE DIVISION].SOURCEDIVDESC, [VARIANCE DIVISION].SOURCESUBP, [VARIANCE DIVISION].BASE, [VARIANCE DIVISION].TYPE, [VARIANCE DIVISION].PCT, [VARIANCE DIVISION].CYMTHPRICE AS CYPRICE, [VARIANCE DIVISION].LYPRICE, [VARIANCE DIVISION].CYADJQTY, [VARIANCE DIVISION].CYADJVAL, [VARIANCE DIVISION].MTHVARIANCE AS VARIANCE, [VARIANCE DIVISION].CYYTDPRICE AS CYTDPRICE, [VARIANCE DIVISION].LYTDPRICE, [VARIANCE DIVISION].CYTDADJQTY, [VARIANCE DIVISION].CYTDADJVAL, [VARIANCE DIVISION].YTDVARIANCEFROM [VARIANCE DIVISION]WHERE ((([VARIANCE DIVISION].INVTYPE) LIKE [FORMS]![REPORTMENU]![INVTYPE]) AND (([VARIANCE DIVISION].TRFTYPE) LIKE [FORMS]![REPORTMENU]![TRFTYPE]) AND (([VARIANCE DIVISION].DIV_CODE) LIKE IIF([FORMS]![REPORTMENU]![PRODCHOICE] = "2",[FORMS]![REPORTMENU]![COMM], "*")) AND (([VARIANCE DIVISION].CYTDADJQTY) <> "0"))WITH OWNERACCESS OPTION;
I have a strange problem I can't figure out. I have a massive query that runs off of about 10 other queries. I use [forms]![Authority].[Emp#] in the query conditions, using the supervisor's employee# to only show them the members on their team on the form.
My problem is that the query/form is working fine for most of the supervisors, but when I enter the employee number of two other supervisors, I get this error: 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.
Those two supervisors don't get the error, the form just doesn't load for them after they click on the button on the switchboard, but if I go into the database window on their pc and try to run it, the error appears.
I currently have a combo list on a purchase order form that has the following event procedure(s) in it so as to open the inventory window as needed to add/edit inventory.
Not in List: Private Sub Combo55_NotInList(NewData As String, Response As Integer) MsgBox "ERROR: ITEM not in list. Double-click this field to add an entry to the list." Response = acDataErrContinue End Sub
On Double-Click: Private Sub Combo55_DblClick(Cancel As Integer) On Error GoTo Err_Combo55_DblClick Dim lngCombo55 As Long
If IsNull(Me![Combo55]) Then Me![Combo55].Text = "" Else lngCombo55 = Me![Combo55] Me![Combo55] = Null End If DoCmd.OpenForm "INVENTORYITEMSform", , , , , acDialog, "GotoNew" Me![Combo55].Requery If lngCombo55 <> 0 Then Me![Combo55] = lngCombo55
This works GREAT on other forms I have however, on this particular form it gives me the following error:
ERROR: You have tried to assign the null value to a variable that is not a variable data type.
I have started from scratch several times....can anyone help? Is there anyway to add a button to the form to open the inventory form and then requery the combo box when closing the inventory form?
I've managed to create an access-database and (fill it with some data), retrieve data from it with asp, but when I try to insert or update data with asp, I get the same error:
"Syntax-error in Insert expression" or "Syntax-error in update expression"
Do I have to "chmod" the database to write to it, or what?
(I usually work with sql-databases, and the code/syntax I believe is not wrong,as it works on another database) Conn.Execute("INSERT INTO tour(date,venue,city,link) VALUES('" & datum & "','" & venue & "','" & city & "','" & link & "')")
We use Microsoft Access databases to enter test data and eventually generate reports.
On 1 computers i get the following error when opening the report:
"Function is not available in expressions in query expression 'Left(.....)"
I figured this is a reference problem so the next thing i did was to pinpoint what reference was causing this error.
It turns out it is the Microsoft DAO 3.6 Object Library. Simply removing the reference and adding it again fixes it and allows you to open reports just fine.
The problem is though, this message comes back every now and then. Which is getting annoying and the person who is making the reports is about to throw his computer out of the window.
Created a form that requires daily work flow info to be entered. Tested the form accepting random typos like "%, # ! etc... Worked just fine. In production received the following long error:"The expression On Click you entered as the event property setting produced the following error: Expression may not result in the name of macro, user defined function or event procedure".
I have 5 different tables that are all linked with foreign keys. When I made my fifth table and linked it to my primary key like I had with the other tables I got the Join expression not supported error.
Hello, first time posting, not sure what info you all need. I'm looking for some help with an if/then statement in access query involving dates. Below is the query I am trying to use, along with the error message. Any ideas?
Domestic Violence CME Required?: IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")
syntax error (comma) in query expression 'IIf(([Date of Next Medical License Renewal]-[Date of Last Domestic Violence Credit (2)])>("yyyy",6),"yes","no")'
The error seems to be in relationship to the number 6. I want a "yes" to show up in the field if the difference is greater then 6 years.
Hello I'm having trouble getting my nested Iif statement to run. Can anyone help??? I've attached a screen shot of the syntax error that I'm receiving. The example code below needs tweaking.
Update [Goodrec-copy3] set [Goodrec-copy3].shortname = Iif (Not Null([shortname]),[Goodrec-copy3].shortname Like "*,JR*" Or ([Goodrec-copy3].[shortname] Like "*, SR*" Or ([Goodrec-copy3].[shortname]) Like "*, II*" Or [Goodrec-copy3].[shortname]) Like "*, III*", InStrRev([shortname])," ",InStr([shortname]," ,")+1,50) &" "& Left([shortname],InStrRev([shortname])," ,")-1) WHERE ((([Goodrec-copy3].[ctype])="I"));
What I'm attempting to accomplish with this query is to keep the field shortname the same if not null and if it doesn't meet the criteria of having a string value of "JR", "SR", "II", or "III". If the field does have a string value of "JR", "SR", "II", or "III" reverse the string (example John Gissom JR) to reflect shortname as such for example: "Gissom JR John".
This error message keeps popping up no matter what I do. "Database can't find the field 'QuotationNumber' referred to in your expression." I tried to define a text box by using a DLookUp function, however that did not work. I deleted the function, and now this error message keeps popping up. I have searched everywhere for an expression with 'QuotationNumber' in it, and I cannot find it. What do I do?
I've been trying to get a query to run but I keep the "type mismatch in expression" error message.It's the Invoice-Product Query in the attached file.I have tried changing the field types, the primary keys and messed around with the relationships but noting seems to work.
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
I'm experiencing an interesting problem with a database I have been working on. For some reason, when I tried to enforce referential integrity between two fields, whose values should have matched perfectly, I got an error that I couldn't because they didn't match. When I ran an unmatched query, I found that the numbers DID match, but for some reason Access wasn't recognizing it. Even when I type the values into the field to "correct" it, it doesn't recognize it; I have to copy and paste from the master table into the child table in order for the values to be recognized as matching.
The same thing happens when I try to filter or query the table; if I type the value, it says no records are found, when I copy and paste, or select from the combo box, it works.
ANY IDEAS?? this is really weird and it's happening on multiple computers so it's not just one system. And, this has never happened before with this file or any other that I've worked on...
SELECT Sum(TBL_MeasuresInstalled.FundingValue) AS ATP_Value FROM TBL_MeasuresInstalled GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
and it returns nothing when there are no records that match the HAVING clause - I want it to return 0 instead
I have searched the forum and people having similar issues have been advised to use the NZ function so I tried
SELECT NZ(Sum(TBL_MeasuresInstalled.FundingValue) ) AS ATP_Value FROM TBL_MeasuresInstalled GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
and
SELECT NZ(Sum(TBL_MeasuresInstalled.FundingValue) , 0) AS ATP_Value FROM TBL_MeasuresInstalled GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
I have someone using my database as an mde, using the 2003 runtime, on a computer that has XP with Office 2007 (including Access 2007). Eveything works fine, except they get error 3085 on a line of code that is running an Insert SQL statement.
What is weird is that the SQL statement does not include the nz function. It does include a simple UDF for rounding, but that UDF doesn't return an error.
I also have code that checks the references on startup, and none of them are coming up as missing or broken. I have also double checked each reference and they have the exact same version number of every reference (dao360.dll, mscomctl.ocx, etc.). So if it is a "diambiguation" issue, I don't know how to fix it, or even identify it.
The code runs on several other computers with various combinations of XP, Vista, Office 2003, and Office 2007 with no problems.
I am stumped. What else could cause this error? Even if you don't really know, but you have a vague idea, please post it because I can't even think of anything else to investigate at this point.:confused:
Morning all The problem whihc i have is that, when trying to open the form "Create Timetable", through the switch board.... a message box appears saying "Syntax error (missing opertor) in query exprssion" Any ideas on why it is showing this message box??? Help would be appricated Thank you
I'm attempting to create a foreign key field in a sub-query by using Left() and Instr() to parse the text of my key field and return the characters prior to the second "-".
Example: 002-C100-4569 ---> 002-C100
When I build a second query with a relationship between the foreign key field previously mentioned with the key field in an associated query, I get the error "Invalid Procedure Call"
