Text to Date Conversions - Solution required 
Author Message
 Text to Date Conversions - Solution required

I have been given an access file to work on which has two text fields which
are meant to be dates.

The text is in Y/M/D format like the following "000813" representing 13 Aug
2000.

Can anyone suggest a way I can translate these Text entries into
recognisable date entries.

There is over 260,000 records.

Ta



Fri, 19 Mar 2004 18:46:36 GMT  
 Text to Date Conversions - Solution required
Hi

Use Mid, Left, right to break string up and convert it to
date, so
CDate(right(str,2) & "/" & mid(str,3,2) & "/" & left
(str,2))

Hope this helps

Ken Reay

Quote:
>-----Original Message-----
>I have been given an access file to work on which has two
text fields which
>are meant to be dates.

>The text is in Y/M/D format like the following "000813"
representing 13 Aug
>2000.

>Can anyone suggest a way I can translate these Text
entries into
>recognisable date entries.

>There is over 260,000 records.

>Ta

>.



Fri, 19 Mar 2004 19:38:09 GMT  
 Text to Date Conversions - Solution required
Sorry, Ken, but that will only work if the user's Regional Settings are
compatible with dd/mm/yy dates.

Much safer is to use the DateSerial function:

DateSerial(CInt(Left$(str,2)), CInt(Mid$(str, 3, 2)), CInt(Right$(str, 2)))

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> Hi

> Use Mid, Left, right to break string up and convert it to
> date, so
> CDate(right(str,2) & "/" & mid(str,3,2) & "/" & left
> (str,2))

> Hope this helps

> Ken Reay
> >-----Original Message-----
> >I have been given an access file to work on which has two
> text fields which
> >are meant to be dates.

> >The text is in Y/M/D format like the following "000813"
> representing 13 Aug
> >2000.

> >Can anyone suggest a way I can translate these Text
> entries into
> >recognisable date entries.

> >There is over 260,000 records.

> >Ta

> >.



Sat, 20 Mar 2004 05:58:31 GMT  
 Text to Date Conversions - Solution required
I hope you aren't looking at birthdates, as I recently met a lady who was
104!

Jeff



Sat, 20 Mar 2004 06:41:54 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Date Stamp vs. Text to Date Conversion

2. Conversion of Dates from text to values

3. Date as Text Conversion Problem

4. HELP!!: Auto conversion text to wrong date ?

5. Conversion of date to text

6. Text to Date conversion?

7. Text to date conversion

8. Type Conversion question: From Text to Date

9. text- date conversion in a query

10. Date conversion in delimited text file


 
Powered by phpBB® Forum Software © phpBB Group