SQL UNION sorting issue

[Cyberwolf0000 via AccessMonster.com]

I have a union SQL statement that is not pulling the data correctly. I am
using this SQL to fill an AtiveX listbox and want it sorted bty the
ScheduleTime field. Here is the SQL statement

SELECT PONumberID, CompanyName, format(ScheduleTime, 'hh:nn') AS STime FROM
qry_CompanySched WHERE ScheduleDate= #5/11/2009# ORDER BY format(ScheduleTime,
'hh:nn') UNION SELECT "" AS PONumberID, NewText AS CompanyName, "" AS STime
FROM tblNew;

As you can see it is a very straight forward statement and I would have
thought it would sort by the STime field. Is this not the case with a Union
statement?

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[BruceM]

How about if you move the ORDER BY to the end:

SELECT PONumberID, CompanyName, format(ScheduleTime, 'hh:nn') AS STime FROM
qry_CompanySched
WHERE ScheduleDate= #5/11/2009#
UNION SELECT Null AS PONumberID,
NewText AS CompanyName, Null AS STime
FROM tblNew
ORDER BY format(ScheduleTime, 'hh:nn')

I made the suggested change of Null instead of zero-length strings, which
I'm not sure will work as intended if PONumberID is a number and STime is
Time/Date. Null and zero-length strings are not the same.

Is this in an Event Procedure? If so, I assume NewText is a string variable
from earlier in the code, or maybe a constant. If not, what is NewText?

 

[Cyberwolf0000 via AccessMonster.com]

Didn't work. It gave me an error stating that "The ORDER BY expression
(format(ScheduleTime, 'hh:nn')) includes fields that are not selected by the
query. Only those fields requested in the first query can included in the
ORDER By clause.

I haven't tried the nulls vs empty strings. The NewText field comes from
tblNew in the second part of the query.

How about if you move the ORDER BY to the end:

SELECT PONumberID, CompanyName, format(ScheduleTime, 'hh:nn') AS STime FROM
qry_CompanySched
WHERE ScheduleDate= #5/11/2009#
UNION SELECT Null AS PONumberID,
NewText AS CompanyName, Null AS STime
FROM tblNew
ORDER BY format(ScheduleTime, 'hh:nn')

I made the suggested change of Null instead of zero-length strings, which
I'm not sure will work as intended if PONumberID is a number and STime is
Time/Date. Null and zero-length strings are not the same.

Is this in an Event Procedure? If so, I assume NewText is a string variable
from earlier in the code, or maybe a constant. If not, what is NewText?

I have a union SQL statement that is not pulling the data correctly. I am
using this SQL to fill an AtiveX listbox and want it sorted bty the

[quoted text clipped - 14 lines]

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[BruceM]

I looked at this too quickly, as it was rather late in the day on Friday.
Now that I look at it more carefully I am still a bit puzzled. A union
query combines data from separate sources into a single recordset, which you
seem to understand. It seems tblNew has the same fields as
qry_CompanySched. What is the connection between the two?

Using Null (or an empty string, if you have allowed for that in your table
design, although I don't think it would work for a Date/Time field) wipes
out the values from those fields as they exist in tblNew, so that you would
see a NewText value (or several, since you are selecting all record from
tblNew), and blanks for PONumberID and STime, assuming both are names in
tblNew. These tblNew values would be added to the recordset created by the
first part of the SQL.

I suggest ordering by an unformatted ScheduleTime field. The Format
function returns a text value, and ordering by that may give you unexpected
results. Add the STime value as a calculated field if you like (as you have
done), but keep it separate from ScheduleTime. Maybe you would have
something like this:

SELECT PONumberID, CompanyName, ScheduleTime,
Format(ScheduleTime, 'hh:nn')
AS STime
FROM qry_CompanySched
WHERE ScheduleDate= #5/11/2009#
UNION SELECT Null AS PONumberID,
NewText AS CompanyName,
Null AS ScheduleTime,
Null as STime
FROM tblNew
ORDER BY ScheduleTime

It would help if I had a clearer idea of what you are trying to accomplish
by combining the two tables (or rather the query and NewTable).

Didn't work. It gave me an error stating that "The ORDER BY expression
(format(ScheduleTime, 'hh:nn')) includes fields that are not selected by
the
query. Only those fields requested in the first query can included in the
ORDER By clause.

I haven't tried the nulls vs empty strings. The NewText field comes from
tblNew in the second part of the query.

How about if you move the ORDER BY to the end:

SELECT PONumberID, CompanyName, format(ScheduleTime, 'hh:nn') AS STime
FROM
qry_CompanySched
WHERE ScheduleDate= #5/11/2009#
UNION SELECT Null AS PONumberID,
NewText AS CompanyName, Null AS STime
FROM tblNew
ORDER BY format(ScheduleTime, 'hh:nn')

I made the suggested change of Null instead of zero-length strings, which
I'm not sure will work as intended if PONumberID is a number and STime is
Time/Date. Null and zero-length strings are not the same.

Is this in an Event Procedure? If so, I assume NewText is a string
variable
from earlier in the code, or maybe a constant. If not, what is NewText?

I have a union SQL statement that is not pulling the data correctly. I
am
using this SQL to fill an AtiveX listbox and want it sorted bty the

[quoted text clipped - 14 lines]

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2