Enter parameter value, but only when a criterion is applied

[terrytek]

I am trying to determine which StudentIDs are enrolled in the current academic year. qryCurrentAcademicYr compares today's date with the start/end dates of the academic year and returns a text string, ex. 2016-17 .
I have a query (which I "wrote" in the Query Design window) to compare a student's academic year with the current academic year; SQL is as follows:

SELECT tblClassAssignment.StudentID, tblClassAssignment.AcademicYr AS Yr, qryCurrentAcademicYr.AcademicYr, StrComp(([qryCurrentAcademicYr].[AcademicYr]),([Yr])) AS [Current]
FROM tblClassAssignment, qryCurrentAcademicYr;

This query works fine, and column Current contains 1, 0, or -1, where 0 means the two strings match. The trouble is, when I enter 0 as the criterion for Current, I get
Enter Parameter Value
Yr
SQL for that looks like:

SELECT tblClassAssignment.StudentID, tblClassAssignment.AcademicYr AS Yr, qryCurrentAcademicYr.AcademicYr, StrComp(([qryCurrentAcademicYr].[AcademicYr]),([Yr])) AS [Current]
FROM tblClassAssignment, qryCurrentAcademicYr
WHERE (((StrComp(([qryCurrentAcademicYr].[AcademicYr]),([Yr])))=0));

Why does the query without the criterion run fine, but with a criterion does not? Query also ran fine, even with criterion, before I replaced "tblClassAssignment.AcademicYr" with Yr, but I need to alias that field name so that I can combine it with some similar information from another query in a union query.

Thanks for your help.

 

[CJ_London]

why not just join on the two fields?

 SELECT tblClassAssignment.StudentID, tblClassAssignment.AcademicYr AS Yr, qryCurrentAcademicYr.AcademicYr, StrComp(([qryCurrentAcademicYr].[AcademicYr]),([Yr])) AS [Current]
FROM tblClassAssignment INNER JOIN qryCurrentAcademicYr
ON [qryCurrentAcademicYr].[AcademicYr]=tblClassAssignment.AcademicYr

 

[terrytek]

why not just join on the two fields?

 SELECT tblClassAssignment.StudentID, tblClassAssignment.AcademicYr AS Yr, qryCurrentAcademicYr.AcademicYr, StrComp(([qryCurrentAcademicYr].[AcademicYr]),([Yr])) AS [Current]
FROM tblClassAssignment INNER JOIN qryCurrentAcademicYr
ON [qryCurrentAcademicYr].[AcademicYr]=tblClassAssignment.AcademicYr

Because I'm not as good at this as you are
That definitely works; thanks!
I'm still curious to know, though, why a query that runs without a criterion asks for a parameter entry when you add a criterion. :banghead:

 

[CJ_London]

usually because there is a typo or you have added a parameter to the query (in query view, click on parameters on the ribbon) - it may still be there even if you have removed it from the query

 

[static]

What CJ_London said.

For some reason field aliases don't work in where clauses, i.e.

select 1 as fld1
where fdl1 = 1

doesn't work but obviously this does

select 1 as fld1
where 1 = 1


Because Yr isn't an actual field, the compiler guesses that it must be a parameter. So,

WHERE StrComp([qryCurrentAcademicYr].[AcademicYr],[Yr])=0

should have been

WHERE StrComp([qryCurrentAcademicYr].[AcademicYr],tblClassAssignment.AcademicYr)=0

 

[pbaldy]

For some reason field aliases don't work in where clauses, i.e.

It's because the WHERE clause is evaluated/processed before the SELECT clause.