Union query with ORDER in VBA doesnt work

[Jack]

I have used the following sql in the query design view and it works just
fine:
SELECT qryEvents.StartDate, qryEvents.EventName, qryEvents.StartTime
FROM qryEvents
UNION Select datestart, eventname,starttime FROM
qryExpandExternalEventDates
Order by StartDate;

However when I use this exact sql statement(pasted in my code) and then use
the sql in the statement
Set rs = CurrentDb.OpenRecordset(sSQL)

I get the following error: Run time error 3351
"The ORDER BY expression (StartDate) includes fields that are not selected
by the query. Only those fields requested in the first query can be included
in an ORDER BY expression.

What is the difference here?

 

[Arvi Laanemets]

Hi

I have used the following sql in the query design view and it works just
fine:
SELECT qryEvents.StartDate, qryEvents.EventName, qryEvents.StartTime
FROM qryEvents
UNION Select datestart, eventname,starttime FROM
qryExpandExternalEventDates
Order by StartDate;

However when I use this exact sql statement(pasted in my code) and then use
the sql in the statement
Set rs = CurrentDb.OpenRecordset(sSQL)

I get the following error: Run time error 3351
"The ORDER BY expression (StartDate) includes fields that are not selected
by the query. Only those fields requested in the first query can be included
in an ORDER BY expression.

What is the difference here?

Try this:
SELECT qryEvents.StartDate, qryEvents.EventName, qryEvents.StartTime FROM
qryEvents UNION Select datestart AS StartDate, eventname,starttime FROM
qryExpandExternalEventDates Order by StartDate;
or even this:
SELECT StartDate, EventName, StartTime FROM qryEvents UNION Select datestart
AS StartDate, EveNtname,StartTime FROM qryExpandExternalEventDates Order by
1;


Arvi Laanemets

 

[John Vinson]

I get the following error: Run time error 3351
"The ORDER BY expression (StartDate) includes fields that are not selected
by the query. Only those fields requested in the first query can be included
in an ORDER BY expression.

What is the difference here?

The SELECT clause contains qryEvents.StartDate; the Order By contains
just unqualified StartDate. Access is just being obnoxiously
literal-minded: these are not identical!

Either remove qryEvents from the first SELECT, add it to the ORDER BY,
or (sneaky) just use the obsolete but still functional syntax

ORDER BY 1

to order by the first field in the Select.

 

[Jack]

I tried what Arvi suggested(use datestart AS StartDate) and came up with the
same error as before.
I also tried what both of you suggested : using
ORDER BY 1 and got the following error:
"too few paramerters. Expected 1."

 

[John Vinson]

I also tried what both of you suggested : using
ORDER BY 1 and got the following error:
"too few paramerters. Expected 1."

Please post the (new) SQL of your union query, and the SQL of
qryEvents. I don't know what's going on!

 

[Jack]

I have two stored queries: qryEvents and qryExpandExternalEventDates:
They are used in another report.

the sql from each query is :
qryEvents:
SELECT DISTINCT Events.StartDate, Events.EventName, Events.StartTime,
Events.Duration, Rooms.Abrev AS Location, Events.ContactPerson,
Events.ContactPhone, Events.Comments, "Reg" AS Type
FROM Events INNER JOIN Rooms ON Events.Room = Rooms.Number
ORDER BY Events.StartDate;

qryExpandExternalEventDates:
SELECT DateAdd("d",[Dayinc]-1,[StartDate]) AS DateStart,
tblExternalEvents.EventName, tblExternalEvents.StartTime,
tblExternalEvents.Duration, tblExternalEvents.Location,
tblExternalEvents.ContactPerson, tblExternalEvents.ContactPhone,
tblExternalEvents.Comments, "Ext" AS Type
FROM tblExternalEvents, tblDayInc
WHERE (((DateAdd("d",[Dayinc]-1,[StartDate]))<=[EndDate]))
ORDER BY DateAdd("d",[Dayinc]-1,[StartDate]);

I have another stored query "qryUnionEvents" which contains the union of
these two queries as follows:
SELECT * FROM qryevents
UNION Select * FROM qryExpandExternalEventDates
ORDER BY Events.startdate;

all of these work just fine.

Now in I want to make use of the first two queries to get get just certain
fields within them. So in vba I construct the following sql(which I have
posted in the original message) as follows:
Code:
sSQLSelect = "SELECT distinct StartDate, EventName, StartTime FROM qryEvents
"
sSQLSelect = sSQLSelect & "UNION Select distinct datestart AS StartDate,
eventname,starttime FROM qryExpandExternalEventDates "

sWhere = "WHERE startdate between #" & dt1 & "# And #" & dt2 & "# "
Combine the strings
sSQL = sSQLSelect & sWhere & " ORDER BY startdate"

this sSQL is used in the
Set rs = CurrentDb.OpenRecordset(sSQL)

The problem seems to be in the ORDER BY clause

That is all of the sql I am using. Like I said I have tried this in the
query window enviroment of access and it works just fine.

 

[John Vinson]

Now in I want to make use of the first two queries to get get just certain
fields within them. So in vba I construct the following sql(which I have
posted in the original message) as follows:
Code:
sSQLSelect = "SELECT distinct StartDate, EventName, StartTime FROM qryEvents
"
sSQLSelect = sSQLSelect & "UNION Select distinct datestart AS StartDate,
eventname,starttime FROM qryExpandExternalEventDates "

sWhere = "WHERE startdate between #" & dt1 & "# And #" & dt2 & "# "
Combine the strings
sSQL = sSQLSelect & sWhere & " ORDER BY startdate"

Well, for one thing, remove the DISTINCT from both queries: UNION
removes duplicates all by itself unless you use UNION ALL.

A couple of questions: Where are dt1 and dt2 defined? If you step
through the code, and display the value of sSQL after constructing it,
what do you get?

The only thing I see is that there might be a missing blank after
qryEvents - which could just be word wrap, and would give a syntax
error anyway. As best as I can tell the SQL is correct based on what
you posted, so I don't know what is causing the error!

 

[Jack]

The dt1 and dt2 are dates . the final ssql comes out as
SELECT distinct StartDate, EventName, StartTime FROM qryEvents UNION Select
distinct datestart AS StartDate, eventname,starttime FROM
qryExpandExternalEventDates WHERE startdate between #9/1/2003# And
#9/30/2003# ORDER BY startdate

 

[Jack]

I made one change to the sql. I included the where clause within each
select statement of the Union. Once I did that the order clause at the end
of the query works just fine.
I dont understand why but it does work now. Thanks for your help in trying
to resolve my problem. I wish there was an explanation.
Thanks
Jack

 

[John Vinson]

I made one change to the sql. I included the where clause within each
select statement of the Union. Once I did that the order clause at the end
of the query works just fine.

I'd have expected it to retrieve far fewer records using the WHERE
clause: each component of the UNION query should return a set of
records determined by its own WHERE, and the UNION will remove the
duplicates (whether within or between portions of the UNION).

 

[Jack]

I have another question about some sql that maybe I should post to the
group, but I thought I would ask first.
I have a table with Date, Time, Event, Room, and other fields.

Some events will have the same date, time but require different rooms.
I want to write an sql to extract all events but for those that have the
same date and time but different rooms, I want only the first one
I know with the DISTINCT , I could extract unique records if the room was
not part of the select, but with the room in the select, I don't know how to
eliminate these records.
Any suggestions?
Jack

 

[John Vinson]

Some events will have the same date, time but require different rooms.
I want to write an sql to extract all events but for those that have the
same date and time but different rooms, I want only the first one
I know with the DISTINCT , I could extract unique records if the room was
not part of the select, but with the room in the select, I don't know how to
eliminate these records.

You'll need to use a TOTALS query; group by the date and time. If you
choose First() as the aggregate function for room, you'll get AN
ARBITRARY record's room value; it will have that date/time, but the
room will be that from the first record in disk storage order, which
is arbitrary and uncontrollable. If you have some other field or
fields which define "the first" you may need to use a Subquery to
select the desired record.

 

[Jack]

It worked great. It turns out that if the user wants to show unique events
what room shows doesnt really matter. They can live with that. So the
frist works ok.
Thanks again John.
Jack