Union query Access on an Interbase DB

Question

I am executing queries from Access 2010 on an Interbase database via ODBC (Easysoft) ver.7. Everything works fine except when i come to fire a Union query such as this:

SELECT TRIP.TRIPDATE, RESERVATION.BOOKINGREF, RESERVATION.LEADNAME, TRIP.DRIVERID, RESERVATION.STATUS, RESERVATION.DATECANCELLED, TRIP.TRANSPORTTYPEID
FROM TRIP INNER JOIN RESERVATION ON TRIP.TRIPID = RESERVATION.ARRIVALTRIPID
WHERE (((TRIP.TRIPDATE) Between #2/1/2012# And #2/29/2012#) AND ((TRIP.DRIVERID)=2) AND ((RESERVATION.DATECANCELLED) Is Null) AND ((TRIP.TRANSPORTTYPEID)=12))


UNION 

SELECT TRIP.TRIPDATE, RESERVATION.BOOKINGREF, RESERVATION.LEADNAME, TRIP.DRIVERID, RESERVATION.STATUS, RESERVATION.DATECANCELLED, TRIP.TRANSPORTTYPEID
FROM TRIP INNER JOIN RESERVATION ON TRIP.TRIPID = RESERVATION.DEPARTURETRIPID
WHERE (((TRIP.TRIPDATE) Between #2/1/2012# And #2/29/2012#) AND ((TRIP.DRIVERID)=2) AND ((RESERVATION.DATECANCELLED) Is Null) AND ((TRIP.TRANSPORTTYPEID)=12));

When I run this query from Access I get

"ODBC --call failed, [Easysoft][Interbase]Dynamic SQL Error, SQL error code = -104, Token unknown -line1,char 0, ((#-104)"

When running the select queries on their own they work fine but when joined via UNION I get this error.

Any help would be appreciated.

thanks

Answer 1

You don't mention if your query is a passthrough query or if you are using linked ODBC tables in an Access query.

If you are using a normal Access query

When using linked ODBC tables in a normal Access query, the Access data engine will rewrite the queries as necessary to make them compatible with the other database engine.
Sometimes, it can fail though.

1. Make sure each SELECT query works and returns correct data independently.

2. Try a simpler UNION query to make sure that the issue comes from the UNION keyword itself.

3. Try UNION ALL

4. Try using a pass-through query instead.

If you are using a pass-through query

Pass-through queries are send verbatim to the ODBC engine, and Access just collects the results without rewriting the query itself.

1. Make sure each SELECT query works as a pass-through query and returns correct data independently.

2. Make sure that the literal dates are properly formatted for Interbase SQL.
   The ones you use are correct for Access SQL, but different databases accept different formats.

3. Try a simpler UNION query using simple SELECT statements involving 1 or 3 fields only.

4. Try UNION ALL.

5. You don't show it in your question, but just in case, if you used an ORDER BY statement, you have to wrap the UNION query.

6. Try to cast the data types of your fields. It may be that some fields's data are incorrectly interpreted and that the union fails because it assumes that the data retrieved is of different types.

7. Try using a standard Access query instead.