Convert text data to Date format

[vmolasi]

Hello,

I have text data containing date and time information in a single field in a table in the following format ``dd.mm.yy hr:mi:se`` (eg 20.08.03 18:30:55). I need to extract the date and time information from this text field and place in two separate Date and Time fields in another table. It worked in Access 97 by using simple text commands (left string and right string) in an Append query.

However it gives me a type conversion error message in Access 2000. Any suggestions to solve this problem?

Thanks

 

[Norris68]

You need to replace the dots in the date part with slashes e.g.
MyDate = CDate(Replace(DateField,".","/&quot)

 

[crawfojl]

Hi:

Just wanted to add an issue that I also came across with text-to-date conversion in Access 2000.

Date in imported txt file is ddmmmyyyy (where "mmm" is alpha, like AUG)and Date needs to be formatted as mm/dd/yyyy.

I created an Update query that looks like this:
Field: Text Field
Table: tblName
Update to: CDate(Left([Text Field],2) & "/" & Mid([Text Field],3,3) & "/" & Right([Text Field],4))

Worked like a charm.

Thx -
crawfojl

 

[sandingdude]

Hi Crazfojl

I just used your syntax:

CDate(Left([Text Field],2) & "/" & Mid([Text Field],3,3) & "/" & Right([Text Field],4))

And it worked great. The only problem I'm having is our date is pulled over as a number. So anything in the year 2000 there is no zero. Example: January 4, 2000 comes over has 104 instead of 000104(YY/MM/DD).

I converted your expresion to look like this:

CDate(Left([Text Field],2) & "/" & Mid([Text Field],3,2) & "/" & Right([Text Field],2)), and I run into the problems with the year. Any suggestions or ideas, I'm stcuk between a rock and hard place.

Thanks.
Dave

 

[SkipVought]

CDate(Left([Text Field],2) & "/" & Mid([Text Field],3,2) & "/" & if(Right([Text Field],1)="0","20","19") & Right([Text Field],2))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sandingdude]

Thanks Skip

Expr1: CDate(Left([IHTDT],2) & "/" & Mid([IHTDT],3,2) & "/" & if(Right([IHTDT],1)="0","20","19&quot & Right([IHTDT],2))

I used the above expression and I get the error - "Undefined Function "if" in expression".

Any ideas?

Thanks for your help

Dave

 

[Golom]

The function needs to be "IIF" ... not "IF". IIF is an immediate if that is executed in-line. "IF" is a VB or VBA code construct.

 

[sandingdude]

Thanks Golom.

I fixed the expression to look like this:

Date: CDate(Left([DateTest],2) & "/" & Mid([DateTest],3,2) & "/" & IIF(Right([DateTest],1)="0","20","19&quot & Right([DateTest],2))

But its still giving me #error in all the fields.

The data we are converting into a date is presently Year/Month/Day and I want it to be Month/Day/Year. Our problem is with anything that happened in 2000 because our data comes over with no zeros in it. So we don't always have a 6 digits number (yymmdd), we have some that looks like 1023 which should be 001023 (October 23, 2000). Will the code I have above fix my problem?

Thanks,

Dave

 

[SkipVought]

After all the Y2K fuss, what idiot would NOT create dates to indicate the full year.

and furthermore,

use a NUMBER with leading ZEROS instead of a STRING OF DIGITS when representing year, month and day -- there's a BIG difference, you know!

You have a disaster ALREADY HAPPENING!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sandingdude]

Skip

I know this is a pain, the boss wants all these different reports and I don't know what to do. Our system is so old and its getting older by the day. Any ideas?

 

[SkipVought]

It is about to break!

Back in 1998 & 1999, MOST conpanies spent ENORMOUS amounts of dough CORRECTING the date format shortcomings of nearly 3 decades.

My recommendation is to tell the boss that you need to hire a competent consultant to advise you how to analyze the databases and programs you have that employ inferior date formats and correct them.

I am surprised that here in 2003, you have not experienced SERIOUS date problems. My guess is that you have, but that ALOT of time has been spent recovering from this kind of stupidity, the cost of which may have ALREADY exceeded what might have been the cost of corrrecting the problem during the last century.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[cmmrfrds]

You can force the text field number to always be a consistent length. Use the Format function.

Dim astr As String
Dim bstr As String
astr = "0104"
bstr = Format(astr, "000000&quot

bstr will result in 000104
This will eliminate the variable length field.

 

[sandingdude]

Awesome thanks.

Now since its still a number field that is converted into a date how do I use the "Between [Start Date] and [End Date]" criteria in the query design. I need to print a report between certain date fields.

Thanks,

Dave

 

[cmmrfrds]

CDate(Mid(Format(Yourdate, "000000&quot,3,2) & "/" & Mid(Format(Yourdate, "000000&quot,5,2) & "/" & Mid(Format(Yourdate, "000000&quot,1,2) )

Rearrange the 6 digits into mm/dd/yy. Microsoft will take care of using the correct century.

 

[sandingdude]

Thanks Cmmrfrds

I still can't use Between [Enter Start Date] And [Enter End Date] on that field to get a range of dates in my query. Any ideas?

Thanks,

Dave

 

[cmmrfrds]

Please show the syntax of your statement - paste in the sql.

 

[SkipVought]

Dave,

You do NOT have DATES! You have a string of digits that LOOK like dates to you and me!

MS will NOT interpret 00/01/01 as Jan 1 2000! (It ACTUALLY interprets 00/01/01 as Jan 1 1900!!!!)

And even if it did, Looking for Jan 1 2000 in your 000000 data would FAIL because the 000000 format is NOT A DATE!

FIX THE SYSTEM!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com