• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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?
0
ouestque
Asked:
ouestque
  • 2
1 Solution
 
mbizupCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now