Solved

Too complex to evaluate

Posted on 2009-05-15
24
369 Views
Last Modified: 2013-11-27
I have a query which has operated fine for years. Now, suddenly I'm getting the "This expression is typed incorrectly or is too complex to be evaluated" message.  I've isolated the problem down to the code string shown below. Without this field definition the query runs happily. Can one of you experts spot the problem in this code?
INST: Right$([SYVZSCH_INSTRUCTOR_NAME],Len([SYVZSCH_INSTRUCTOR_NAME])-InStr(1,[SYVZSCH_INSTRUCTOR_NAME]," "))

Open in new window

0
Comment
Question by:ChickenFarmer
  • 11
  • 7
  • 3
  • +1
24 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24399969
Try this expression instead:

INST: Mid([SYVZSCH_INSTRUCTOR_NAME], InStr(1,[SYVZSCH_INSTRUCTOR_NAME]," ") + 1)

If that does not work, please post the whole SQL statement.
0
 

Author Comment

by:ChickenFarmer
ID: 24400002
Hi Patrick,
Didn't fly, so here's the entire query in sql.
SELECT DISTINCT PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE, tblConfirmationDataFromLocal.LastFirst, PRINUSER_SYVZCCD.SYVZCCD_ID, PRINUSER_SYVZCCD.SYVZCCD_PIDM, PRINUSER_SYVZCCD.SYVZCCD_CRN AS CRN, PRINUSER_SYVZCCD.SYVZCCD_RSTS_CODE, QryCrsSchedPROD.SYVZSCH_PTRM_CODE AS PTRM, QryCrsSchedPROD.SYVZSCH_START_DATE, QryCrsSchedPROD.SYVZSCH_END_DATE, QryCrsSchedPROD.SYVZSCH_BEGIN_TIME AS START, QryCrsSchedPROD.SYVZSCH_END_TIME AS [END], QryCrsSchedPROD.BLDG AS BLDG, QryCrsSchedPROD.ROOM, [BLDG] & " " & [ROOM] AS BANNERROOM, QryCrsSchedPROD.TERM_NAME, tblConfirmationDataFromLocal.SYVZCNF_ESTS_CODE, QryCrsSchedPROD.SYVZSCH_CRSE_TITLE AS TITLE, Right$([SYVZSCH_INSTRUCTOR_NAME],Len([SYVZSCH_INSTRUCTOR_NAME])-InStr(1,[SYVZSCH_INSTRUCTOR_NAME]," ")) AS Inst, IIf([SYVZSCH_INSTRUCTOR_NAME_2] Is Not Null,(Right$([SYVZSCH_INSTRUCTOR_NAME_2],Len([SYVZSCH_INSTRUCTOR_NAME_2])-InStr(1,[SYVZSCH_INSTRUCTOR_NAME_2]," "))),"") AS INST2, QryCrsSchedPROD.SYVZSCH_INSTRUCTOR_NAME_3 AS INST3, tblConfirmationDataFromLocal.SYVZCNF_ASCD_CODE, IIf(Right([SYVZCNF_TERM_CODE],2)<>"93",IIf([SYVZSCH_PTRM_CODE]="WK1","ONE","TWO"),"") AS WEEK, tblConfirmationDataFromLocal.SYVZCNF_BLDG_DESC, tblConfirmationDataFromLocal.SYVZCNF_BEGIN_DATE, IIf([SYVZCCD_RSTS_CODE]="WL","Sorry, you are waitlisted for this course") AS REGSTAT, tblTimeConv.Time, QryCrsSchedPROD.SYVZSCH_TERM_CODE, [SYVZCNF_BLDG_CODE] & " " & [RmAttrib] AS HseBrk, tblTimeConv.Note, IIf([SYVZCNF_END_DATE]<[FirstSat],"1",IIf([SYVZCNF_BEGIN_DATE]>[FirstMon],"2")) AS Stay
FROM tblPopSel, tblTermNames INNER JOIN (((PRINUSER_SYVZCCD INNER JOIN QryCrsSchedPROD ON PRINUSER_SYVZCCD.SYVZCCD_CRN = QryCrsSchedPROD.SYVZSCH_CRN) INNER JOIN tblConfirmationDataFromLocal ON (PRINUSER_SYVZCCD.SYVZCCD_PIDM = tblConfirmationDataFromLocal.SYVZCNF_PIDM) AND (PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE = tblConfirmationDataFromLocal.SYVZCNF_TERM_CODE)) INNER JOIN tblTimeConv ON (QryCrsSchedPROD.SYVZSCH_END_TIME = tblTimeConv.End) AND (QryCrsSchedPROD.SYVZSCH_BEGIN_TIME = tblTimeConv.Start)) ON tblTermNames.TermCode = PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE
WHERE (((PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE)=[Forms]![frmSwitchboard].[cboChooseTerm]) AND ((PRINUSER_SYVZCCD.SYVZCCD_ID)=IIf([forms]![frmSwitchboard].[cboSelectmode]="Select One or More",[tblPopSel].[ID],[SYVZCNF_ID])) AND ((tblConfirmationDataFromLocal.SYVZCNF_ESTS_CODE)<>"WD") AND ((QryCrsSchedPROD.SYVZSCH_TERM_CODE)=[Forms]![frmSwitchboard].[cboChooseTerm]))
ORDER BY tblConfirmationDataFromLocal.LastFirst;

Open in new window

0
 

Author Comment

by:ChickenFarmer
ID: 24400180
MatthewsPatrick,

It's about 5:25 here and, lest my wife shoot me, I need to start heading home. This being Friday, I'll check my email over the weekend but won't be able to implement your solution suggestion until I get back to the office on Monday.

I hope you'll hang in with me until I get this fixed.

0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24401457
Or just for fun, try just using Right(), instead of the Right$() (with the $ Type declaration character for strings)
Access 2007 may not like this.

We Experts here have heard countless stories of code that has "Worked fine for years" untill upgrading to Access 2007.
;-)
It seems that MS has "tightened up" the VBA syntax.
So somethings that it let you get away with in previous versions is not allowed anymore.

Also try to avoid running Access 2003 format databases in Access 2007.
You can probably do it without any issues on most databases, but in dealing with hunderds of questions here, I have seen more than a few 2003 dbs that choked under Access 2007.
If you are converting all users to Access 2007, then create a blank Access 2007 db and import all of the 2003 objects into it.

Finally, make sure you install SP2 for Office 2007. (just released 4/24/2009)

JeffCoachman
 

0
 

Author Comment

by:ChickenFarmer
ID: 24411702
thanks Jeff. It's helpful to know that others are dealing with Access 2003/2007 issues.

The syntax change ("$" to "") didn't work.

I'll check on the service pack and if not installed, get it and try this again.

If still dead, I'll do as you suggest and create a new 2007 db and import all the objects into it. (Wow, that'll take a while.)

I'll post here when I get all that done.

Thanks for the advice.

0
 

Author Comment

by:ChickenFarmer
ID: 24413138
Okay, I've installed Office SP2. Restarted and still got the error.
I created a new db in Access 2007 and imported all the objects from the converted 2007 db. Restarted and still get the error.

Here's the sql for the failing query again. The issue seems to be in the definition of "INST".

Help?
SELECT DISTINCT PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE, tblConfirmationDataFromLocal.LastFirst, PRINUSER_SYVZCCD.SYVZCCD_ID, PRINUSER_SYVZCCD.SYVZCCD_PIDM, PRINUSER_SYVZCCD.SYVZCCD_CRN AS CRN, PRINUSER_SYVZCCD.SYVZCCD_RSTS_CODE, QryCrsSchedPROD.SYVZSCH_PTRM_CODE AS PTRM, QryCrsSchedPROD.SYVZSCH_START_DATE, QryCrsSchedPROD.SYVZSCH_END_DATE, QryCrsSchedPROD.SYVZSCH_BEGIN_TIME AS START, QryCrsSchedPROD.SYVZSCH_END_TIME AS [END], QryCrsSchedPROD.BLDG AS BLDG, QryCrsSchedPROD.ROOM, [BLDG] & " " & [ROOM] AS BANNERROOM, QryCrsSchedPROD.TERM_NAME, tblConfirmationDataFromLocal.SYVZCNF_ESTS_CODE, QryCrsSchedPROD.SYVZSCH_CRSE_TITLE AS TITLE, Right$([SYVZSCH_INSTRUCTOR_NAME],Len([SYVZSCH_INSTRUCTOR_NAME])-InStr(1,[SYVZSCH_INSTRUCTOR_NAME]," ")) AS INST, IIf([SYVZSCH_INSTRUCTOR_NAME_2] Is Not Null,(Right$([SYVZSCH_INSTRUCTOR_NAME_2],Len([SYVZSCH_INSTRUCTOR_NAME_2])-InStr(1,[SYVZSCH_INSTRUCTOR_NAME_2]," "))),"") AS INST2, QryCrsSchedPROD.SYVZSCH_INSTRUCTOR_NAME_3 AS INST3, tblConfirmationDataFromLocal.SYVZCNF_ASCD_CODE, IIf(Right([SYVZCNF_TERM_CODE],2)<>"93",IIf([SYVZSCH_PTRM_CODE]="WK1","ONE","TWO"),"") AS WEEK, tblConfirmationDataFromLocal.SYVZCNF_BLDG_DESC, tblConfirmationDataFromLocal.SYVZCNF_BEGIN_DATE, IIf([SYVZCCD_RSTS_CODE]="WL","Sorry, you are waitlisted for this course") AS REGSTAT, tblTimeConv.Time, QryCrsSchedPROD.SYVZSCH_TERM_CODE, [SYVZCNF_BLDG_CODE] & " " & [RmAttrib] AS HseBrk, tblTimeConv.Note, IIf([SYVZCNF_END_DATE]<[FirstSat],"1",IIf([SYVZCNF_BEGIN_DATE]>[FirstMon],"2")) AS Stay
FROM tblPopSel, tblTermNames INNER JOIN (((PRINUSER_SYVZCCD INNER JOIN QryCrsSchedPROD ON PRINUSER_SYVZCCD.SYVZCCD_CRN = QryCrsSchedPROD.SYVZSCH_CRN) INNER JOIN tblConfirmationDataFromLocal ON (PRINUSER_SYVZCCD.SYVZCCD_PIDM = tblConfirmationDataFromLocal.SYVZCNF_PIDM) AND (PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE = tblConfirmationDataFromLocal.SYVZCNF_TERM_CODE)) INNER JOIN tblTimeConv ON (QryCrsSchedPROD.SYVZSCH_END_TIME = tblTimeConv.End) AND (QryCrsSchedPROD.SYVZSCH_BEGIN_TIME = tblTimeConv.Start)) ON tblTermNames.TermCode = PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE
WHERE (((PRINUSER_SYVZCCD.SYVZCCD_TERM_CODE)=[Forms]![frmSwitchboard].[cboChooseTerm]) AND ((PRINUSER_SYVZCCD.SYVZCCD_ID)=IIf([forms]![frmSwitchboard].[cboSelectmode]="Select One or More",[tblPopSel].[ID],[SYVZCNF_ID])) AND ((tblConfirmationDataFromLocal.SYVZCNF_ESTS_CODE)<>"WD") AND ((QryCrsSchedPROD.SYVZSCH_TERM_CODE)=[Forms]![frmSwitchboard].[cboChooseTerm]))
ORDER BY tblConfirmationDataFromLocal.LastFirst;

Open in new window

0
 

Author Comment

by:ChickenFarmer
ID: 24413310
Here's another clue: If I run the query for a limited number of records (<15) it runs perfectly. But it chokes on more than that.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24413621
Well with the, SELECT DISTINCT, IIF functions, Instr Functions, and joins to other queries that very well may also be fairly complex, I can see why perhaps you are getting the "Too Complex Error".

The way I see it you have a few options:
1. Create separate queries and join them into one query, to get the same result.
2. Use User Defined Functions in place of the IIF Formulas
3. Click the "Request Attention" button and ask that this Q be placed in the SQL Syntax zone.
This is more of an SQL issue than an Access issue.
An Expert in the SQL Zone may be able to optimize this query.

JeffCoachman
0
 

Author Comment

by:ChickenFarmer
ID: 24413640
Thanks again, Jeff.  I'll appeal to the sql folks and see if we can't fix it where it sits.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24416138
Try to attach a model database; small but eproducing the issue. Compact and repair; no MBs!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24416349
<no MBs>
No "(M)other(B)oard(s)?
:-S
0
 

Author Comment

by:ChickenFarmer
ID: 24416380
Okay, the Compact and Repair process is running. I'll post again when done and I can retest.
w.r.t. attaching a model database... tough to do as most of the data comes off our administrative system and I can't easily reproduce.

Like boag2000, what do you mean by no MB's? Can you explain please?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24416422
:-)
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24416431
(M)ega (B)ytes
0
 

Author Comment

by:ChickenFarmer
ID: 24416435
Compact and Repair complete. query still yields the same error.

Help?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24416586
It is difficult to recreate the issue here. If you need help attach the database, but as I mentioned with just the data to reproduce the effect. Remove all not related objects like forms and reports. Compact and attach!
0
 

Author Comment

by:ChickenFarmer
ID: 24416616
Okay, I'll create a maketable query to replicate part of the data which comes off the server. Then I'll redirect a query with the error-causing field to that new table. I'll delete everything else and upload it. (back in a few minutes...)
0
 

Author Comment

by:ChickenFarmer
ID: 24416816
Okay,
I've set up a file with just a single table and one query which contains the unhappy sql string.

I had to backward save it so I could upload it here.

right away I have a data mismatch error, which suggests that the commands used in the query don't expect to be dealing with a straight text field.

Now that's as smart as I get on this stuff. So please let me know what you can do.

Nearly 5pm here. so I'll look for your reponse in the morning. Thanks for any suggestions.

ChickenFarmer.mdb
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 24417079
Modify inst: to use IIF as in INST2 field.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24417092
try this:

ChickenFarmer.mdb
0
 

Author Closing Comment

by:ChickenFarmer
ID: 31582120
You've shown that while a null value in the INST field didn't trip up 2003, it stopped 2007 cold in its tracks. Thanks for walking me through the analysis and spotting that. We're up an running again!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24426891
You are welcome!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question