Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 18th, 2004, 12:42 PM
Registered User
Join Date: May 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kshantik
Default Conversion from string to date

My date field is a string, in the MMDDYY format eg: 012904

I want to write a query to pull all the records with date greater than '01/29/04'

I have oracle background and there were nice functions like TO_DATE(columnname,'MM/DD/YY') to use in my query and I am wondering if there is anything like this in MS Access. If not, how do I solve this?

Old May 18th, 2004, 02:27 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

I would urge you to change the datatype of your date field FROM string TO date in the first place. You'll avoid all these problems in the future.

As for dates already in your system as text fields, you can do this:

dtmNewDate = DateSerial(Right(strOldDate, 2), Left(strOldate, 2), _
   Mid(strOldate, 3, 2))

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Old May 19th, 2004, 12:17 PM
Registered User
Join Date: May 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to kshantik

Thanks! It works!

I have not designed the database, someone else created the field as text type instead of date. I don't think they want to change that now.

Thanks for your help.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conversion from string to type 'Date' error jellyjune BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 9 August 12th, 2007 02:05 AM
Conversion from string "" to type 'Date' jellyjune .NET Framework 1.x 1 August 11th, 2007 04:12 AM
Date Conversion ashokparchuri General .NET 2 May 31st, 2005 01:26 AM
Date conversion AFK SQL Language 2 February 4th, 2005 05:14 PM
Conversion part of string to date MRvLuijpen Access 5 May 18th, 2004 02:31 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.