I actually have two separate questions, but since they both have to do with the same query I will post them both here.

#1 I have a student database am designing a query (and report) which I would like to parameterize by site, grade, and date. The grade and date seem to work fine, but the school always returns the "too complex or incorrect" error message and I don't know why. It is at the level of the grade, there are no null values and the error does NOT occur when I leave it blank (it returns all values). When site isn't parameterized it works fine and is sortable. Why is this happening and how can I fix it?

I don't know SQL at all, but here it is:

SELECT [Standard Avg - Individual].[Student ID], [Standard Avg - Individual].First, [Quiz Avg - Individual].Last, [Quiz Avg - Individual].Site, [Quiz Avg - Individual].Grade, [Quiz Avg - Individual].Expr1, [Strand Avg - Individual].Strands, [Strand Avg - Individual].AvgOfScore, [Standard Avg - Individual].AvgOfScore, [Standard Avg - Individual].Standard, [Standard Avg - Individual].Description, [Standard Avg - Individual].CountOfScore, [Strand Avg - Individual].CountOfScore, [Quiz Avg - Individual].CountOfScore, [Enter Beginning Date] & " - " & [Enter Ending Date or leave blank to see all] AS Expr2
FROM Results INNER JOIN ([Standard Avg - Individual] INNER JOIN ([Quiz Avg - Individual] INNER JOIN [Strand Avg - Individual] ON ([Quiz Avg - Individual].Site = [Strand Avg - Individual].Site) AND ([Quiz Avg - Individual].[Student ID] = [Strand Avg - Individual].[Student ID]) AND ([Quiz Avg - Individual].First = [Strand Avg - Individual].First) AND ([Quiz Avg - Individual].Last = [Strand Avg - Individual].Last) AND ([Quiz Avg - Individual].Grade = [Strand Avg - Individual].Grade)) ON ([Standard Avg - Individual].Site = [Strand Avg - Individual].Site) AND ([Standard Avg - Individual].Grade = [Quiz Avg - Individual].Grade) AND ([Standard Avg - Individual].First = [Quiz Avg - Individual].First) AND ([Standard Avg - Individual].Last = [Quiz Avg - Individual].Last) AND ([Standard Avg - Individual].[Student ID] = [Quiz Avg - Individual].[Student ID]) AND ([Standard Avg - Individual].Strands = [Strand Avg - Individual].Strands)) ON Results.[Student ID] = [Quiz Avg - Individual].[Student ID]
GROUP BY [Standard Avg - Individual].[Student ID], [Standard Avg - Individual].First, [Quiz Avg - Individual].Last, [Quiz Avg - Individual].Site, [Quiz Avg - Individual].Grade, [Quiz Avg - Individual].Expr1, [Strand Avg - Individual].Strands, [Strand Avg - Individual].AvgOfScore, [Standard Avg - Individual].AvgOfScore, [Standard Avg - Individual].Standard, [Standard Avg - Individual].Description, [Standard Avg - Individual].CountOfScore, [Strand Avg - Individual].CountOfScore, [Quiz Avg - Individual].CountOfScore, [Enter Beginning Date] & " - " & [Enter Ending Date or leave blank to see all]
HAVING ((([Quiz Avg - Individual].Site)=[Enter Site]) AND (([Quiz Avg - Individual].Grade)=[Enter Grade (or leave blank for all)])) OR ((([Enter Grade (or leave blank for all)]) Is Null));


#2 I tried to make a form for the parameters instead of dialogue boxes, but there is an issue. I am using quiz averages for students which span dates, which is why the parameter is a range of dates and date (the field in my tables) is NOT a field in the query. If I make it a field and then make Start/End Dates in a form (which I did successfully) then every assessment has a discreet value and the averaged values either disappear or just repeat for each discreet date value. Is there a way to have the date range itself by a field in the query so I can reference it in a form? If this is really hard I am mostly OK with just have 4 dialogue boxes.

Thanks so much for your help!

fixed #1 (had to set the primary key from the table into the query instead of a text lookup field)

still need help with #2 though!

thanks!

You should be able to have it in a WHERE clause without being in the SELECT or GROUP BY clauses. In design view, uncheck the "Show" box.