• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • 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.

Thanks,

Deeky
0
deeky
Asked:
deeky
1 Solution
 
GRayLCommented:
Use:

Cdate(myDateString)
0
 
Rey Obrero (Capricorn1)Commented:

cdate(Format([TextDatefield],'mm/dd/yyyy')) as dDate
0
 
harfangCommented:
Hello,

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,
(°v°)
0
 
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,

Deeky
0
 
GRayLCommented:
Thanks, glad I could help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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