Query bracketing issue (1 Viewer)

terrytek

Registered User.
Local time
Today, 09:27
Joined
Aug 12, 2016
Messages
75
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:

Code:
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:

Code:
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

Banishment Pending
Local time
Today, 08:27
Joined
May 11, 2011
Messages
11,613
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

Super Moderator
Staff member
Local time
Today, 13:27
Joined
Feb 19, 2013
Messages
16,555
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?
 

Users who are viewing this thread

Top Bottom