Question Getting syntax error in the query

[mounika]

Hi Lag bolt,
Thanks for the immediate help on that day, I discussed your suggestions with our director but he asked to implement in a different way.. I wrote a query below , but it's giving some syntax errors , can u please help to resolve them. I am getting the below error , can you please check and let me know where we were missing the syntax ?? Thanks in advance

syntax error (missing operator) in query expression Switch(shift='Y'," DateDiff("n", max_time, min_time)", shift='N', "DateDiff("n", min_time, max_time)")
(
SELECT HD.HolderName



Query:

select HD.HolderName, HD.JobTitle, IO.IODate,
Switch(shift='Y'," DateDiff("n", max_time, min_time)", shift='N', "DateDiff("n", min_time, max_time)")
(
SELECT HD.HolderName, HD.JobTitle, IO.IODate, shift, min(io.iotime) as min_time, max(io.iotime) as max_time
FROM
(
SELECT HD.HolderName, HD.JobTitle, IO.IODate, IO.IOTime,'N' as shift
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between #27/01/2014# and #31/01/2014#
AND ((IO.IOTime >= '8:00:00' AND IO.IOTime < '18:00:00') OR (IO.IOTime >= '18:00:00' AND IO.IOStatus='Exit'))
UNION ALL
SELECT HD.HolderName, HD.JobTitle, IO.IODate, IO.IOTime,'Y' as shift
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between <27/01/2014> and <31/01/2014>
AND ((IO.IOTime >= '18:00:00' AND IO.IOStatus='Entry'))
UNION ALL
SELECT HD.HolderName, HD.JobTitle, IO.IODate-1, IO.IOTime,'Y' as shift
FROM HolderData AS HD, IOData AS IO
WHERE HD.HolderNo = IO.HolderNo and
HD.DepartmentNo IN ('0008', '0009') and
IO.IODate between <27/01/2014> and DateAdd ("d", 1, <31/01/2014>)
AND IO.IOTime <= '7:00:00'
)
GROUP BY HD.HolderName, HD.JobTitle, IO.IODate, shift
) ;

 

[mounika]

My bad.. I just realized that "FROM" key word was missing from the outer most query.

Thanks
Mounika