Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 April 3rd, 2007, 07:38 PM
Authorized User
Join Date: Mar 2007
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date Conversion from txt field

Hi All

I need some help with being able to convert a txt field to a date field.
The Field format = txt
The data looks like 070402 (which if noted is yymmdd)

I can format this to look like 07/04/02 with no problems
However as our systems are all set to dd/mm/yyyy the data is incorrect.
The 070402 is imported from a csv, and the 07/04/02 is a result of an update query that reads
UPDATE B2BOrdersStage SET B2BOrdersStage.[Date] = Format(([Date]),"yy/mm/dd");
Date being the name of the field

However no matter what i try i cannot seem to get the date to read format dd/mm/yyyy eg 02/04/2007
Can anyone help


Old April 4th, 2007, 10:05 AM
Friend of Wrox
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

Use cDate and string manipulation to get the value changed to a proper date format:
MyTable.[Date Field Name] = cDate(Mid(sImportDate, 3, 2) & "/" & Right(sImportDate, 2) & "/" & Left(sImportDate, 2))
Format doesn't change or convert values, it only changes how it is displayed.

Old April 6th, 2007, 07:04 AM
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

Use a combination of the LEFT, RIGHT, and MID functions with the DATESERIAL() function.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Similar Threads
Thread Thread Starter Forum Replies Last Post
Uplad txt field of size greater than 12 mb rapraj PHP Databases 3 October 28th, 2007 09:36 AM
Importing date field in a large txt file CongoGrey Access 2 July 10th, 2007 06:44 AM
DATE within txt box alannoble Excel VBA 1 December 22nd, 2004 12:36 PM
Date conversion lily611 General .NET 6 December 6th, 2004 12:41 AM
Haw to convert txt date to 8/11/2004 Deputy Dog C# 1 September 1st, 2004 07:54 PM

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