How to convert Varchar into Datetime?

Question

Using Access 2003

Table

ENO Time

001 020000
001 040000
001 220000
002 030000
002 050000
005 230000

So on…,

Time Date type is Varchar in the Database. How to convert a Varchar into Datetime?

Here I want to get a total of time for the ENO.

Select eno, sum (time) from table group by eno

It showing error like – data type mismatch criteria error

Expected Output

001 26:00:00
002 08:00:00

So on…,

Need Query Help.

Answer 1

You are best off working in seconds. Use the Mid() function to break up the string and calculate the number of seconds, sum the times, and then format the result however you like. If you write it as a single query you'll need to use the same long expression in three different places, so for improved readability I would do it as two queries. The first is:

SELECT eno, sum(val(mid(time,1,2))*3600+val(mid(time,3,2))*60+val(mid(time,5,2))) AS secs
FROM table
GROUP BY eno;

Save this query in access as "enosums", for example. The second query is:

SELECT eno, format(secs/3600, "00:") & format((secs/60 Mod 60), "00:") & format(secs Mod 60, "00")
FROM enosums;

The reason I suggest this method is that even if you manage to convert to datetime values (which you can do by using the Mid() function and concatenation operator to convert the "hhmmss" string into "hh:mm:ss" format and then apply the TimeValue function), there is no easy way to print this in the output format you need because the Format() function will only go up to 23:59:59 and then wrap back to 00:00:00.