How to convert from string to a date in query

Posted on 2006-04-14
Last Modified: 2012-05-05
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.


Question by:deeky
    LVL 44

    Accepted Solution


    LVL 119

    Expert Comment

    by:Rey Obrero

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

    Expert Comment


    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,

    Author Comment

    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,

    LVL 44

    Expert Comment

    Thanks, glad I could help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now