Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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 2nd, 2004, 01:16 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default Convert Text to Date/Time in Access

Hi,

   I have been given a mess of data, and DOB and other date values are in Text like this DDMMYYYY. When I use the Table designer to convert the Date/Time, Access can't change the values.

   Is there a script or some other function that will allow me to change this data?

Thanks,
mmcdonal

mmcdonal
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old April 3rd, 2004, 01:49 PM
Authorized User
 
Join Date: Feb 2004
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'd run an update query to convert the string field data into a date value and place the data in a new field defined as a date data type after the data is in the table as a string. CDate should do the trick except that it thinks mmddyyyy. It's generally smart enough to realize that:

23022001

means February 23, 2001, but

03022001

will be interpreted as March 2, 2001.

You can write a format conversion function and return the date datatype for your update:

Public Function StrToDate(strIn As String) As Variant
    If Len(strIn & "") Then
        StrToDate = cDate(Mid$(strIn, 3, 2) & "/" & _
          Left$(strIn, 2) & "/" & Right$(strIn, 4))
    Else
        StrToDate = Null
    End If
End Function

The function returns variants in order to handle nulls in case the field has no data.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
 
Old April 4th, 2004, 05:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Another option you could do, is (and this is only really practical if it's a once off thing) change your computer's regional settings, so that the date is formatted as ddmmyyyy.
This should allow it just fine.
Being an Australian, this is how we do dates here anyway, which causes some problems, as SQL only likes mmddyyyy, so I have to do conversions if I'm building SQL from values in text boxes and the like.
So although the Query Designer shows 31/12/2001, the SQL Statement will show 12/31/2001 anyway.

But if it's something you're going to have to do a lot, then Jürgen has you on the right path

I am a loud man with a very large hat. This means I am in charge
 
Old April 7th, 2004, 06:39 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I have the dates in the proper format, but they are stored as text, with a date input mask. DO I need to covert them to Date/Time in order to do date arithmetic with them?

mmcdonal
 
Old April 7th, 2004, 10:13 AM
Authorized User
 
Join Date: Feb 2004
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Access does a great many datatype conversions implicitly. Whether it will produce the result you are seeking depends on the date arithmetic function and that is not tolerable.

Date/Tive fields are of type double with the integer portion translating to a date, and the fractional portion to the time. Adding 1 increments the date by one day. Subracting .5 decrements the time by 12 hours. That will reduce the day by one if the date/time in the variable is before noon. Functions like DateAdd(), Month() DateDiff() generally require a date time datatype to operate on but, assuming the sring format can be interpreted as a date, all that is necessary is to add '#' delimiters around the string type.

The best and only sure way to proceed is to store as a date/time field.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
 
Old June 15th, 2004, 12:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Looks like the answer was "yes".. did you undertand it?

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee
 
Old June 15th, 2004, 12:14 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I have blocked this email address from my email. You are one tenacious smug bastard.

mmcdonal
 
Old June 15th, 2004, 12:19 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,101
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Oh no! I've been blocked (how did he do that? he doesn't know my email address)...

More likely you blocked the WROX site.

I won't comment on if that's good or bad.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee





Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert UNIX Time to MS Access Corey Linux 4 April 7th, 2009 11:52 AM
convert data from nvarchar to Date Time rahulgzb SQL Server 2000 4 September 7th, 2007 09:45 PM
Convert text from excel to proper date in access bprodman Access VBA 1 June 18th, 2007 03:48 PM
Convert UNIX Time to MS Access Corey Access 1 January 23rd, 2006 12:32 AM
Changing Text Data to Date/Time jjc9809 Access 4 January 22nd, 2006 07:41 PM





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