Query bracketing issue

[terrytek]

I have a qryMostRecentTest where I was using LAST to get the latest date. Due to LAST returning unexpected results, I changed it to MAX, and now it works correctly by returning the most recent date.

There is another qryStudentTotalHoursSinceLastTest that uses this MaxOfTestDate. When I try to run it, I get the error message

Invalid bracketing of name '[qryMostRecentTest.LastOfTestDate]'.

How can I be getting this message if that parameter no longer appears in the query??

Here is qryStudentTotalHoursSinceLastTest, which returns the error:

SELECT tblStudents.StudentID, Count(tblStudents.StudentID) AS CountOfStudentID, tblStudents.FirstName, tblStudents.LastName, qryPairHoursSinceLastTestSum.SumOfTutoringHours, qryClassHoursSinceLastTestSum.SumOfClassHours, (Nz([SumOfTutoringHours],0)+(Nz([SumOfClassHours],0))) AS TotalHours, qryMostRecentTest.MaxOfTestDate
FROM (((tblStudents INNER JOIN qryPairOrClassByYear ON tblStudents.StudentID=qryPairOrClassByYear.StudentID) LEFT JOIN qryPairHoursSinceLastTestSum ON tblStudents.StudentID=qryPairHoursSinceLastTestSum.StudentID) LEFT JOIN qryClassHoursSinceLastTestSum ON tblStudents.StudentID=qryClassHoursSinceLastTestSum.StudentID) INNER JOIN qryMostRecentTest ON tblStudents.StudentID=qryMostRecentTest.StudentID
GROUP BY tblStudents.StudentID, tblStudents.FirstName, tblStudents.LastName, qryPairHoursSinceLastTestSum.SumOfTutoringHours, qryClassHoursSinceLastTestSum.SumOfClassHours, (Nz([SumOfTutoringHours],0)+(Nz([SumOfClassHours],0))), qryMostRecentTest.MaxOfTestDate
HAVING (((qryMostRecentTest.MaxOfTestDate) Is Not Null));

and here is qryMostRecentTest:

SELECT tblTesting.StudentID, tblStudents.LastName, tblStudents.FirstName, Max(tblTesting.TestDate) AS MaxOfTestDate
FROM tblTestType INNER JOIN (tblStudents INNER JOIN tblTesting ON tblStudents.StudentID=tblTesting.StudentID) ON tblTestType.TestTypeID=tblTesting.TestTypeID
GROUP BY tblTesting.StudentID, tblStudents.LastName, tblStudents.FirstName
ORDER BY tblStudents.LastName;

BTW, in qryMostRecentTest, I didn't just change LAST to MAX; I deleted that field from the query and put it back in, choosing MAX instead of LAST.

Thank you.

 

[plog]

First, I'd give your aggragate fields (COUNT, SUM, MAX, etc.) more meaningful names than what Access does:

...MAX(TestDate) AS RecentTestDate..

Easier to type and recognize what they mean to you. For the bracket issue--you don't need any. Or you need 4.

Correct - > TableName.FieldName
Correct -> [TableName].[FieldName]

Lastly, F-word Last() in it's A-word with a big knobby D-word (can I cuss on this forum? Better safe than sorry). It's a horrible function that should not be in Access. Same with its inbred cousin First(). They don't do what people expect. You are doing right with MAX()

 

[CJ_London]

you've got lots of queries there - do any of them relate to [qryMostRecentTest.LastOfTestDate]? and I assume the missing square brackets in the middle is a typo?

Also check your query properties - have you got a sort or order property set?