Solved

Too complex to evaluate

Posted on 2009-05-15
24
362 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Try to attach a model database; small but eproducing the issue. Compact and repair; no MBs!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<no MBs>
No "(M)other(B)oard(s)?
:-S
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ChickenFarmer
Comment Utility
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
Comment Utility
:-)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
(M)ega (B)ytes
0
 

Author Comment

by:ChickenFarmer
Comment Utility
Compact and Repair complete. query still yields the same error.

Help?
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Modify inst: to use IIF as in INST2 field.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
try this:

ChickenFarmer.mdb
0
 

Author Closing Comment

by:ChickenFarmer
Comment Utility
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
Comment Utility
You are welcome!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now