Solved

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

Posted on 2007-11-15
5
197 Views
Last Modified: 2010-03-20
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
Comment
Question by:ouestque
  • 2
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 20293541
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
 
LVL 7

Expert Comment

by:Stephen Lappin
ID: 20297271
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
 

Author Comment

by:ouestque
ID: 20314145
MBzip got the answer first so she gets the points. Thanks you both!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20315612
Glad to help out!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question