• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

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.


1 Solution

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now