Why wont' this append query work? Help!

[Crispywafers]

I have an append query that is used to add new orders to the order
table. It's supposed to add orders if the student is "active" and the
student does NOT already have an order with a SchoolYear date of
__________. The School Year should be a parameter that is typed in
when the query runs. Otherwise, it should add the order.

I dont' understand, but when I run it the first time (to a blank
table) it appends correctly and if you rerun it on the same table, it
will correctly NOT add the students again. However, if I change the
SchoolYear to a new school year, the query SHOULD add new orders again
to that table becaue it's a new school year so this is a completly
different order-- however it just keeps saying no rows were appended.
Help??

Code:

PARAMETERS SchoolYear DateTime;
INSERT INTO Orders ( StudentID, EarlyBird, Grade, Dismissal, Discount,
TotalTuition, JulyTuition, DecTuition, MarchTuition, SchoolYear )
SELECT Student_Records.StudentID, Student_Records.EarlyBird,
Student_Records.Grade, Student_Records.Dismissal,
Student_Records.Discount,
FormatCurrency(PrintTuitionQuery([Dismissal],[Grade],[EarlyBird],[Discount]))
AS TotalTuition, PrintJDTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS JulyTuition, PrintJDTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS DecTuition, PrintMTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS MarchTuition, [SchoolYear] AS SchoolYear
FROM Student_Records
WHERE (((Exists (SELECT * FROM Orders
WHERE StudentID = Student_Records.StudentID
AND SchoolYear = [SchoolYear]))=False) AND
((Student_Records.Active)=True));

 

[John Spencer (MVP)]

Well, you shouldn't have a parameter that has the same name as a field. As far
as the SQL is concerned they are the same. Try changing the parameter name to
[Specify School Year]. You query was specifically checking the value of the
SchoolYear field against the Value of the SchoolYear field, not against whatever
value you were putting in for the parameter.


PARAMETERS [Specify School Year] DateTime;
INSERT INTO Orders ( StudentID, EarlyBird, Grade, Dismissal, Discount,
TotalTuition, JulyTuition, DecTuition, MarchTuition, SchoolYear )
SELECT Student_Records.StudentID, Student_Records.EarlyBird,
Student_Records.Grade, Student_Records.Dismissal,
Student_Records.Discount,
FormatCurrency(PrintTuitionQuery([Dismissal],[Grade],[EarlyBird],[Discount]))
AS TotalTuition, PrintJDTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS JulyTuition, PrintJDTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS DecTuition, PrintMTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS MarchTuition, [SchoolYear] AS SchoolYear
FROM Student_Records
WHERE (((Exists (SELECT * FROM Orders
WHERE StudentID = Student_Records.StudentID
AND SchoolYear = [Specify School Year]))=False) AND
((Student_Records.Active)=True));

I have an append query that is used to add new orders to the order
table. It's supposed to add orders if the student is "active" and the
student does NOT already have an order with a SchoolYear date of
__________. The School Year should be a parameter that is typed in
when the query runs. Otherwise, it should add the order.

I dont' understand, but when I run it the first time (to a blank
table) it appends correctly and if you rerun it on the same table, it
will correctly NOT add the students again. However, if I change the
SchoolYear to a new school year, the query SHOULD add new orders again
to that table becaue it's a new school year so this is a completly
different order-- however it just keeps saying no rows were appended.
Help??

Code:

PARAMETERS SchoolYear DateTime;
INSERT INTO Orders ( StudentID, EarlyBird, Grade, Dismissal, Discount,
TotalTuition, JulyTuition, DecTuition, MarchTuition, SchoolYear )
SELECT Student_Records.StudentID, Student_Records.EarlyBird,
Student_Records.Grade, Student_Records.Dismissal,
Student_Records.Discount,
FormatCurrency(PrintTuitionQuery([Dismissal],[Grade],[EarlyBird],[Discount]))
AS TotalTuition, PrintJDTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS JulyTuition, PrintJDTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS DecTuition, PrintMTriQuery([Dismissal],[Grade],[EarlyBird],[Discount])
AS MarchTuition, [SchoolYear] AS SchoolYear
FROM Student_Records
WHERE (((Exists (SELECT * FROM Orders
WHERE StudentID = Student_Records.StudentID
AND SchoolYear = [SchoolYear]))=False) AND
((Student_Records.Active)=True));

 

[crispywafers]

Thank you! Thank you! Thank you!
That worked.

That was driving me insane.