?
Solved

Too complex to evaluate

Posted on 2009-05-15
24
Medium Priority
?
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 3
  • +1
24 Comments
 
LVL 93

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
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

 
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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

800 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