Union Query not working, but did 2 weeks ago

[coldude]

Hi there,
Was there a recent update or something that may have affected a Union Query in MS Access 2003 (operating system XP Pro, all current updates installed)?

In our database we have a union query that display available sessions and time period of availability, this is done by identifying the session with **available** at the start of each line for some reason it works on some computers and not others.

Does anyone know anything that may effect this code in operating 100% of the time?


thanks in advance.. Colin



The SQL code used is a follows:-

SELECT '** available **','', '',format(A.session_end, "dd/mm/yyyy") as START_DATE,format(A.session_end,"hh:nn") as START_TIME, format(B.session_start, "HH:MM") AS END_TIME, int(DateDiff("n",a.session_end,b.session_start) / 60) AS Expr1, (DateDiff("n",a.session_end,b.session_start) - expr1 * 60) AS Expr2,a.session_start
FROM session_last AS A, session_last AS B
WHERE (((B.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])) And (B.session_start)=(SELECT MIN(C.session_start)
FROM session_last C
WHERE C.simulator_code = A.simulator_code
AND C.session_start BETWEEN (([Forms]![Bookings]![StartDateInput])) AND (([Forms]![Bookings]![EndDateInput]))
AND C.session_start > A.session_start
)) AND (((A.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo])) AND ((A.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])))
AND ((B.simulator_code)=[A].[simulator_code]) AND ((.[session_start]-[A].[session_end])<>0))


UNION SELECT '',C.customer_code, C.session_type, format(C.session_start, "dd/mm/yyyy"), format(C.session_start,"HH:MM"), format(C.session_end,"HH:MM"), int(DateDiff("n", C.session_start, C.session_end) / 60) AS Expr3, (DateDiff("n", C.session_start, C.session_end) - expr3 * 60) AS Expr4,c.session_start
FROM session_last AS C
WHERE (C.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput]))
AND (((C.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo]))
ORDER BY 9, 4, 5, 1;


END of SQL code..

 

[WayneRyan]

Colin,

On the surface it looks like an OK Union query. There appear to be the
same number and type of columns.

How does it not work?
Does it throw an error, or just give "unexpected" results?
Does each half of the query return the proper results?

btw,
What is --> ORDER BY 9, 4, 5, 1;

Wayne

 

[coldude]

Thanks for your reply Wayne as im stuck with this one..

ok, i'm not given any errors actual Access Errors as such, it just fails to display the text "**avaliable**" at the start of the query, the query looks to run ok when on it's own, but when i display it on a Form in a List Box all the other text is displayed but not the "**avaliable**" as per this attachment.

I should have mentioned this earlier, and thinking about it i could have prepared this question a little better. somthing for next time i guess.


sorry, i Inherited this database so im not really sure whar the ORDER piece means.


any tips?


regards


Colin

Colin,

On the surface it looks like an OK Union query. There appear to be the
same number and type of columns.

How does it not work?
Does it throw an error, or just give "unexpected" results?
Does each half of the query return the proper results?

btw,
What is --> ORDER BY 9, 4, 5, 1;

Wayne