How to convert from string to a date in query
Posted on 2006-04-14
I'm linking to a table in a proprietary PVX database (ProvideX) using an ODBC driver provided by the manufacturer.
I link to the table from Access to the PVX system and I have raw data that I can READONLY from this PVX system, I cannot write back to it or change structure.
I have a linked table in Access to the PVX database that I need to populate for a few fields. The date is stored as text and in the format yyyymmdd (20020406). I have created a query that builds this in the correct format using two variables in the query and I have it in the format mm/dd/yyyy. However, it is still not an official "date" field, it's still a text string. How can I get the field data type to change to a date in a query because I'm sort of using this query like a table for another source elsewhere.
I have another database in MS-OUTLOOK ODBCing to the query that rebuilt the date. The program populating that database is not happy with the date as a string, as it will not recognize it.
One solution that I have found is to MAKE TABLE QUERY a table based on the query that fixes my date because I can change the field data type on the local table, then have my MS-OUTLOOK ODBC link to that. It works, but now if the data information changes back as step 1 in the PVX system, it doesn't propagate back to the MS-OUTLOOK database at the end because the MAKE-TABLE query needs to be manually run.
Is there a way to change from text to date within a query. I have the string formatted correctly, but not the correct data-type.