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

more