Too complex to evaluate

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

ChickenFarmerAsked:
Who is Participating?
 
hnasrCommented:
Modify inst: to use IIF as in INST2 field.
0
 
Patrick MatthewsCommented:
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
 
ChickenFarmerAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ChickenFarmerAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
ChickenFarmerAuthor Commented:
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
 
ChickenFarmerAuthor Commented:
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
 
ChickenFarmerAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
ChickenFarmerAuthor Commented:
Thanks again, Jeff.  I'll appeal to the sql folks and see if we can't fix it where it sits.
0
 
hnasrCommented:
Try to attach a model database; small but eproducing the issue. Compact and repair; no MBs!
0
 
Jeffrey CoachmanMIS LiasonCommented:
<no MBs>
No "(M)other(B)oard(s)?
:-S
0
 
ChickenFarmerAuthor Commented:
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
 
hnasrCommented:
:-)
0
 
hnasrCommented:
(M)ega (B)ytes
0
 
ChickenFarmerAuthor Commented:
Compact and Repair complete. query still yields the same error.

Help?
0
 
hnasrCommented:
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
 
ChickenFarmerAuthor Commented:
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
 
ChickenFarmerAuthor Commented:
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
 
hnasrCommented:
try this:

ChickenFarmer.mdb
0
 
ChickenFarmerAuthor Commented:
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
 
hnasrCommented:
You are welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.