SQL Code for report works in 2010; has issues in 2007.

[Stang70Fastback]

Hey guys. I've got some code here that seems to present a bit of an issue. It basically joins two tables together, but ONLY includes records that are unique to either side (so basically joining two tables and not including records where the employee number shows up in both tables.) It works fine in 2010, but in 2007, I only get the data from the first table in the resulting report, not the second. Can anyone looking at this see something obvious that would present an issue in 2007? I'm guessing it has to do with the UNION statement, because everything after that has to do with merging the second table, which is what isn't showing up.

The two tables being merged are 'ShiftChangeA' and 'ShiftChangeB.' I'm thinking now I should have put this in a different forum section. I originally put it here because this SQL code is in the Record Source of my Report.

SELECT ShiftChangeA.[Employee Number], ShiftChangeA.Category, ShiftChangeA.[Shift Description], ShiftChangeA.Date, ShiftChangeA.[Start Time], ShiftChangeA.[End Time], ShiftChangeA.Duration, ShiftChangeA.[Day Of Week], ShiftChangeA.[Employee First Name], ShiftChangeA.[Employee Last Name], ShiftChangeA.Route, ShiftChangeA.Bus FROM ShiftChangeA LEFT JOIN ShiftChangeB ON ShiftChangeA.[Employee Number] = ShiftChangeB.[Employee Number] WHERE (((ShiftChangeB.[Employee Number]) Is Null) AND ((ShiftChangeA.[Shift Description]) Not Like "*20AT*" And (ShiftChangeA.[Shift Description]) Not Like "*60AT*" And (ShiftChangeA.[Shift Description]) Not Like "*90AT*" And (ShiftChangeA.[Shift Description]) Not Like "*AT2*" And (ShiftChangeA.[Shift Description]) Not Like "*AT3*" And (ShiftChangeA.[Shift Description]) Not Like "*AT4*")); UNION SELECT ShiftChangeB.[Employee Number], ShiftChangeB.Category, ShiftChangeB.[Shift Description], ShiftChangeB.Date, ShiftChangeB.[Start Time], ShiftChangeB.[End Time], ShiftChangeB.Duration, ShiftChangeB.[Day Of Week], ShiftChangeB.[Employee First Name], ShiftChangeB.[Employee Last Name], ShiftChangeB.Route, ShiftChangeB.Bus FROM ShiftChangeB LEFT JOIN ShiftChangeA ON ShiftChangeB.[Employee Number] = ShiftChangeA.[Employee Number] WHERE (((ShiftChangeA.[Employee Number]) Is Null) AND ((ShiftChangeB.[Shift Description]) Not Like "*50B*" And (ShiftChangeB.[Shift Description]) Not Like "*52B*"));

 

[JHB]

What happen if you run the second part alone, do you get any data then?
The only thing I can see a "little wrong" is the ";" in "... *AT4*")); UNION SELECT ...", try when remove it!