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 January 22nd, 2006, 03:08 PM
Registered User
 
Join Date: Jan 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Changing Text Data to Date/Time

I have a Table in Access with an Issue Date field that is all Text Data. The data appears as 100189, 092305, 020198, 123105, and 10200. I need for this data to have slashes in between the numbers so that Access will know that this is a Date/Time field instead. I want to query this tabel pulling records for a certain time-frame.

I know that an Update Query can be made to do this using SQL, but I am new to SQL and I need all 15,000 records done with MM/DD/YY.

Is there some SQl Code that will do this?


jjc9809@yahoo.com


Joel Carmack
 
Old January 22nd, 2006, 03:17 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Try this in the update to field - Assuming you are using the query builder...

LEFT([IssueDate],2) & "/" & MID([IssueDate],3,2) & "/" & RIGHT([IssueDate],4)



Mike
EchoVue.com
 
Old January 22nd, 2006, 03:19 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Sorry, the last digit should be 2, since there are only 2 numbers for the year.

Mike
EchoVue.com
 
Old January 22nd, 2006, 03:47 PM
Registered User
 
Join Date: Jan 2006
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by echovue
 Sorry, the last digit should be 2, since there are only 2 numbers for the year.

Mike
EchoVue.com

Mike,

Thanks so much! It worked! I am new to ACCESS and SQL. I'm glad there are knowledgeable people out there. I am hoping I can get experienced like you.

It will take some time, but I feel that I can learn it.

Again, Thanks


Joel

Joel Carmack
 
Old January 22nd, 2006, 07:41 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Joel,

Good to hear, and welcome to the forum. I don't know if I rank even close to being and expert, but leveraging the combined knowledge of everyone who subscribes to it, there will be precious little that you can't figure out how to do.

Mike


Mike
EchoVue.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
convert data from nvarchar to Date Time rahulgzb SQL Server 2000 4 September 7th, 2007 09:45 PM
changing data source location at run time sandeepjakhotya Crystal Reports 0 April 16th, 2006 05:40 AM
Getting Problem in DATE/TIME Data type in MS Acces selam70 Classic ASP Databases 2 September 4th, 2004 02:31 PM
Convert Text to Date/Time in Access mmcdonal Access VBA 7 June 15th, 2004 12:19 PM
Displying Date/time inside a text field spraveens HTML Code Clinic 1 November 14th, 2003 12:06 PM





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