Access:Union query: Where a field meets a condition not working

I have a form called "Students". On this form is a textbox called "txtStudent". Also on this form is a listbox called "StudentList". I also have two tables ("Table1"and "Table2") both tables have the following fields ("StudentName", "SS#", "Grade","Status")

When someone types a name in the textbox ("txtStudent") and presses enter it runs the code below:

[Forms]![Students]![StudentList].RowSource = "SELECT [StudentName], [SS#], [Grade], [Status] FROM TABLE1 UNION SELECT [StudentName], [SS#], [Grade], [Status] FROM TABLE2 WHERE [StudentName] =[Forms]![Students]![txtStudent] ORDER BY [StudentName];"

This code is supposed to display in the list box only those records with a "StudentName"  equal to what the user typed in the textbox ("xtStudent"). Nonetheless the code displays all records anyway. Will someone help me make the code work?
ouestqueAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Give this a try:

[Forms]![Students]![StudentList].RowSource = "SELECT * FROM (SELECT [StudentName], [SS#], [Grade], [Status] FROM TABLE1 UNION SELECT [StudentName], [SS#], [Grade], [Status] FROM TABLE2 ) WHERE [StudentName] =[Forms]![Students]![txtStudent] ORDER BY [StudentName]"
0
 
Stephen LappinSenior TechnologistCommented:
The first side of your UNION has no where clause, hence is returning all rows. Try this:

Forms]![Students]![StudentList].RowSource = "SELECT [StudentName], [SS#], [Grade], [Status] FROM TABLE1 WHERE [StudentName] =[Forms]![Students]![txtStudent] UNION SELECT [StudentName], [SS#], [Grade], [Status] FROM TABLE2 WHERE [StudentName] =[Forms]![Students]![txtStudent] ORDER BY [StudentName];"
0
 
ouestqueAuthor Commented:
MBzip got the answer first so she gets the points. Thanks you both!!!
0
 
mbizupCommented:
Glad to help out!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.