Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 2nd, 2004, 01:16 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #2 (permalink)  
Old April 3rd, 2004, 01:49 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
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
Reply With Quote
  #3 (permalink)  
Old April 4th, 2004, 06:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
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
Reply With Quote
  #4 (permalink)  
Old April 7th, 2004, 07:39 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #5 (permalink)  
Old April 7th, 2004, 11:13 AM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
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
Reply With Quote
  #6 (permalink)  
Old June 15th, 2004, 01:10 PM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
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
Reply With Quote
  #7 (permalink)  
Old June 15th, 2004, 01:14 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #8 (permalink)  
Old June 15th, 2004, 01:19 PM
Friend of Wrox
Points: 3,489, Level: 24
Points: 3,489, Level: 24 Points: 3,489, Level: 24 Points: 3,489, Level: 24
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Central, NJ, USA.
Posts: 1,102
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert UNIX Time to MS Access Corey Linux 4 April 7th, 2009 12:52 PM
convert data from nvarchar to Date Time rahulgzb SQL Server 2000 4 September 7th, 2007 10:45 PM
Convert text from excel to proper date in access bprodman Access VBA 1 June 18th, 2007 04: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



All times are GMT -4. The time now is 03:11 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.