query criteria not working

[George]

Good night.


I create a field called “Comment” in a query based on a “score” field in a table. The “Comment” field returns the results: “Pass” or “Fail”. I am trying to list all the “pass” records by entering the word “pass” in the criteria row in the “Comment” field. What I am getting is a popup box saying “Enter parameter value”. It just would not run.
Can this problem be solved easily?

[John_G]

Hi -

It may not be that field that is causing the problem. Can you post the SQL if the whole query?

John

[George]

actually I posted a simplified model of the problem; but here is the real and complete query as requested:

SELECT [last Name] & " " & [First Name] AS Name, Programs.[Prog Name], Courses.[Course Name], [Class by Course].Group, Attendance.CourseMark, Attendance.Atten, Attendance.ExamMark, [Class by Course].Location, nz([CourseMark])+nz([ExamMark]) AS Total, [Class by Course].CourseMarksAllocated, [Class by Course].ExamMarksAllocated, IIf([Class by Course]![CourseMarksAllocated]=True And [Class by Course]![ExamMarksAllocated]=True,[Attendance]![CourseMark]*60/100+[Attendance]![ExamMark]*40/100,IIf([Class by Course]![CourseMarksAllocated]=True And [Class by Course]![ExamMarksAllocated]=False,CourseMark,IIf([Class by Course]![CourseMarksAllocated]=False And [Class by Course]![ExamMarksAllocated]=True,ExamMark))) AS Remarks, IIf([Remarks]>49,"Pass") AS [Comment 1], [Class by Course].Year, IIf([Remarks]>40 And [Remarks]<50,"Supplimental",IIf(nz([Remarks])<41,"Repeat","")) AS [Comment 2], Attendance.Note
FROM Courses INNER JOIN (Programs INNER JOIN (Student INNER JOIN ([Class by Course] INNER JOIN Attendance ON [Class by Course].ClassbyCourseID = Attendance.[Class by course ID]) ON Student.[Student ID] = Attendance.StudentID) ON Programs.[Program Code] = [Class by Course].[Program Code]) ON Courses.[Course ID] = [Class by Course].[Course ID]
WHERE (((IIf([Remarks]>40 And [Remarks]<50,"Supplimental",IIf(nz([Remarks])<41,"Repeat","")))="Repeat"));

[JoeM]

I find that sometimes access gets a little fickle with nesting calculations and then trying to apply criteria to those nested calculations right in the same query.
One way around that is to save your query without that criteria (let's call it "Query1", for example).
Then use this query in a new query, and enter your criteria there, i.e.

SELECT Query1.*
FROM Query1
WHERE Query1.Comment="PASS";