This expression is typed incorrectly, or is too complex to be evaluated

[NSAMSA]

Hi all:



I've looked through other posts regarding this error and have not found anything specifically helpful for my particular situation. I'm hoping I can gain some clarity here.

I have the following query design:

SELECT qryCourseRecDept.EmployeeNumber, qryCourseRecDept.EmployeeID, qryCourseRecDept.CourseName, qrySchedCourse.MonthCriteria, qryCourseRecDept.LastDate, qrySchedCourse.Department, qrySchedCourse.SchedMonth
FROM qrySchedCourse INNER JOIN qryCourseRecDept ON (qrySchedCourse.Course = qryCourseRecDept.CourseName) AND (qrySchedCourse.Department = qryCourseRecDept.GeneralDept)
WHERE (((qryCourseRecDept.LastDate)<[MonthCriteria]));

Its the last qualifier which is causing the issue. The LastDate is the date in which the course was completed as a short date. The MonthCriteria is actually a built date. I have a course schedule that assigns month (e.g. "January"), and a Year, (e.g. "2015") I created a column for my month list, (where the month is derived from) that specifies for January that the MonthParam = "1/1/" and I build the MonthCriteria as [MonthParam]&[SchedYear] as a short date. This is all in the qrySchedCourse. I then give a criteria in the table as >=[MonthCriteria] to find compliant courses. A course can occur more than once. So, someone could have completed the course 1/5/2015 and be compliant for the January schedule, but not be compliant for the December schedule which has a MonthCriteria of 12/1/2015. If I don't add the criteria, the table runs fine showing the last date for January and December as the same for the course and the accurate MonthCriteria date for each. I kind of get what's wrong, but have no clue as to how to make the query run the way I would like. Any help would be greatly appreciated.

Thank you

[RuralGuy]

I don't see what is wrong but the parser in Access can get confused with some SQL. You can often resolve this by breaking it down into more than one query.

[InsuranceGuy]

Hi all:

[MonthParam]&[SchedYear] as a short date.

The error described has to do with datatype mismatches most frequently. If you are concatenating these two fields exactly as described, you're probably getting a string datatype instead of a date datatype. Occassionally Access will not translate string dates effectively and you need to force the matter using "CDate([MonthParam]&[SchedYear])".

Cheers,

Jeff

[NSAMSA]

Fantastic! That was it Jeff. I'm so happy when the fixes are easy like that. Close it down boys.

Thank you!

Nick