How to rewrite TSQL query for use in Microsoft Access 2003: TSQL Convert

Question

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?

Answer 1

Can you not use the normal Access function Str(...) ?

Update: Dont have Access here to try it, but isn't there a parameter which tells Access to just use the sql as pass throught (without modifying it) ?

Answer 2

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