Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeSQL Server is pretty good about implicitly converting text to time. If the time for is really odd, then you may need to use the CONVERT function or create a custom function.

Can you do this for all 30 tables in a single query? MAYBE using a union query, but don't count on it.|||I am not using SQL Server but Microsoft Access.

I time had an odd format but i have managed to manipulate it to the format 00:00:00.

I only need to convert the field from text to date/time. I have tried convert() and CDate() but i cannot make it to work.

My table is named "01" and the field "Duration".

Can anyone provide me with the full code to make the change.

I will use "Macros" and "RunSQLQuery" to make the converion for all tables.

This is the last problem i have to solve in order to make it work. I have been working on this database for the last week.

Please note that i am new in SQL with MS Access. I have started only one moth ago|||I would suggest that you post this question in the MS-Access (http://www.dbforums.com/f84) forum. They have more experience with the Access GUI and might be able to give you better suggestions.

I tried using Cdate("13:23:45") in an Access query, and it worked nicely for me. I suspect that that is the conversion that you need, but I'm not certain about what code you need to derive a properly formatted time string.

-PatP|||Ok, I thought of something else.

There is no need to change the data type of the table. I just created a query with all the fields of the table but for Duration i put: CDate([Duration]). The query returns the results as Date/Time and from there i can perform the calculations i require.

It works.

Thanks again, problem solved.|||Gee, you just have to love it when you inadvertanly solve a problem!

-PatP