Access DB - Union Select Many to one

[tcadieux]

Cannot figure out why this query gives me a 'Synatax error in Order By Clause'

sSQL=" SELECT tblLineUpStuff.LineUpStuff AS theText, tblLineUpStuffRelations.Order"
sSQL=sSQL & " FROM tblLineUpStuffRelations INNER JOIN tblLineUpStuff "
sSQL=sSQL & " ON tblLineUpStuffRelations.LineUpStuffID = tblLineUpStuff.LineUpStuffID Text"
sSQL=sSQL & " UNION "
sSQL=sSQL & " SELECT tblShow.Project AS theText, tblLineUpShowRelations.Order"
sSQL=sSQL & " FROM tblLineUpShowRelations INNER JOIN tblShow "
sSQL=sSQL & " ON tblLineUpShowRelations.ShowID = tblShow.ShowID "
sSQL=sSQL & " Order By theText"