473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Convert 01-JAN-1999 from text to date

1
I have a database with over 100,000 records and the dates are all stored as text fields 01-JAN-1999. I need to convert them to a date field and have tried numerous options on my own and in examples posted on threads. I am in a crunch to get this done, need a quick fix from the experts! Thanks!
Dec 22 '09 #1
2 2108
missinglinq
3,532 Expert 2GB
Replace FauxDateField with the actual name of your control/field:

In VBA behind a form:

CDate(Me.FauxDateField)

In a Query

RealDateField:CDate([FauxDateField])


Having said that, for the VBA date functions, such as DateAdd() and DateDiff(), using the Text field you currently have will work just fine. Access/VBA is very accommodating that way, if it looks like a date it will consider it to be a date!.

Welcome to Bytes!

Linq ;0)>
Dec 22 '09 #2
NeoPa
32,556 Expert Mod 16PB
You may need to write a query to do the update of the data for you. Clearly it would involve the technique as described here by Linq. The SQL for the query would be something very like :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [YourTable]
  2. SET [FixedDateField]=CDate([FauxDateField])
You are responsible for creating the new field to store the data in, and changing the SQL to match the names of your fields. We can help, but only with the relevant information provided.
Dec 22 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

13
by: Hako | last post by:
I try this command: >>> import string >>> string.atoi('78',16) 120 this is 120 not 4E. Someone can tell me how to convert a decimal number to hex number? Can print A, B, C,DEF. Thank you.
2
by: Jason | last post by:
In VB.NET, when I use System.Convert.ToDouble(string Val) to convert a string variable to double variable, I got something interesting: Dim stringVal As String = "101.01" Dim doubleVal As Double...
10
by: David Garamond | last post by:
The Postgres manual says: The AT TIME ZONE construct allows conversions of time stamps to different time zones. I'd guess most people would think what's meant here is something like "unit...
1
by: Benny Schudel | last post by:
hello I've tried to convert some milliseconds to a time format. $ms = 100000 // 1min 40sec echo strftime('%H:%M:%S', $ms/1000); i expect the result is: "00:01:40" but the result ist:...
6
by: google0 | last post by:
I know this is a trivial function, and I've now spent more time searching for a surely-already-reinvented wheel than it would take to reinvent it again, but just in case... is there a published,...
1
by: shapper | last post by:
Hello, I am trying to convert an Asp.Net 2.0 XML sitemap file to a Google's sitemap file. I am posting the formats of both files. How can I do this? Thank You,
2
by: eddiekwang | last post by:
I have a table called class_time. and the column name is start_time. datatype is datetime. id start_time 2 1900-01-01 15:00:00.000 4 1900-01-01 17:12:00.000 i want to the convert the...
5
by: RyanL | last post by:
I'm a newbie! I have a non-delimited data file that I'd like to convert to delimited. Example... Line in non-delimited file: 0139725635999992000010100534+42050-102800FM-15+1198KAIA Should...
7
by: crs27 | last post by:
Hai, Im new to sql server... I want to convert a field in a table to DateTime. The column(Column Name-DDateTime , Data type String) has data in this format 01-04-2008 15:12:52 i want...
5
by: | last post by:
Hi group, Is there an easy way to convert a UInt16 value in to a string which presents it in a binary format. I need a conversion with a fixed length. so 6 must be presented as...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.