Hi there,
Was there a recent update or something that may have affected a Union Query from being displayed in a Form List box with 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 over that last couple of weeks it now displays a blank column and doesn’t display the text “**available**”
”works on some computers and not others.
I have backed up the database and taken a copy of it home and it all works fine (was created at work), but fails to function correctly at Work.
Does anyone know anything that may affect this code in operating 100% of the time?
I'm lost with this one, Thanks in advance.. Colin
The Union Query 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..
Was there a recent update or something that may have affected a Union Query from being displayed in a Form List box with 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 over that last couple of weeks it now displays a blank column and doesn’t display the text “**available**”
”works on some computers and not others.
I have backed up the database and taken a copy of it home and it all works fine (was created at work), but fails to function correctly at Work.
Does anyone know anything that may affect this code in operating 100% of the time?
I'm lost with this one, Thanks in advance.. Colin
The Union Query 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..
