How to convert from string to a date in query

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.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:

cdate(Format([TextDatefield],'mm/dd/yyyy')) as dDate

Get rid of the two intermediate "variables". You have a field, let's call it TextDate, with dates like "20020423". You can directly get the date in one expression:

    CDate(Format( TextDate, '@@@@/@@/@@' ))

CDate accepts the yyyy/mm/dd format without any problem.

If needed, the reverse if of course: Format( TrueDate, 'yyyymmdd' )

Hope this helps,
deekyAuthor Commented:
Absolutely this helps...

I will look at harfang solution in a later version when I streamline.  Right now I got what I have working with the GRayL solution.

Always a pleasure,

Thanks, glad I could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.