I'd like to rewrite this query for Microsoft Access 2003:
SELECT t1.PERSONID ,t1.CARDEVENTDATE ,MIN(t1.CARDEVENTTIME1) AS Intime ,MAX(t2.CARDEVENTTIME1) AS Outtime FROM ( SELECT PERSONID , CARDEVENTDATE , FUNCTIONKEY , CONVERT(VARCHAR(10), SUBSTRING(CARDEVENTTIME, 1, 2) + ':' + SUBSTRING(CARDEVENTTIME, 3, 2) + ':' + SUBSTRING(CARDEVENTTIME, 5, 2), 8) AS CARDEVENTTIME1 FROM T_CARDEVENT WHERE (FUNCTIONKEY = 'A')) AS t1 LEFT OUTER JOIN (SELECT PERSONID ,CARDEVENTDATE, CARDEVENTDAY, FUNCTIONKEY ,CONVERT(VARCHAR(10), SUBSTRING(CARDEVENTTIME, 1, 2) + ':' + SUBSTRING(CARDEVENTTIME, 3, 2) + ':' + SUBSTRING(CARDEVENTTIME, 5, 2), 8) AS CARDEVENTTIME FROM T_CARDEVENT AS T_CARDEVENT_3) AS t2 ON t1.PERSONID = t2.PERSONID AND t1.CARDEVENTDATE = t2.CARDEVENTDATE GROUP BY t1.PERSONID, t1.CARDEVENTDATE )
The above works in SQL Server, but in Access I've tried to run this query. It produces an error in convert
.
How should I modify my query to suit Access 2003? What's the suitable equivalent for TSQL's Convert function in Access 2003?
The sample below may suit, if you use SQL Server compatible Syntax.
In t2, you appear to be missing 1 from CARDEVENTTIME, so I added it to give CARDEVENTTIME1. You also appear to have an extra bracket at the end.
SELECT t1.PERSONID
,t1.CARDEVENTDATE
,MIN(t1.CARDEVENTTIME1) AS Intime
,MAX(t2.CARDEVENTTIME1) AS Outtime
FROM ( SELECT PERSONID
, CARDEVENTDATE
, FUNCTIONKEY
, CDate(Mid(CARDEVENTTIME, 1, 2) + ':' + Mid(CARDEVENTTIME, 3, 2) + ':' + Mid(CARDEVENTTIME, 5, 2))
AS CARDEVENTTIME1
FROM T_CARDEVENT
WHERE (FUNCTIONKEY = 'A')) AS t1
LEFT OUTER JOIN (SELECT PERSONID
,CARDEVENTDATE, CARDEVENTDAY, FUNCTIONKEY
, CDate(Mid(CARDEVENTTIME, 1, 2) + ':' + Mid(CARDEVENTTIME, 3, 2) + ':' + Mid(CARDEVENTTIME, 5, 2))
AS CARDEVENTTIME1
FROM T_CARDEVENT AS T_CARDEVENT_3)
AS t2
ON t1.PERSONID = t2.PERSONID
AND t1.CARDEVENTDATE = t2.CARDEVENTDATE
GROUP BY t1.PERSONID, t1.CARDEVENTDATE