Converting Text to Date

  • Thread starter KimTong via AccessMonster.com
  • Start date
K

KimTong via AccessMonster.com

Hi,

Is anybody can tell me, how to convert from text data to Date?. I have a
table imported from Text file, text file only recognize text & number only.
One of the field (we call: TxtDate) they have is '20070515" (yyyymmdd) has to
be in date format. How can I convert it to our regular format (mm/dd/yy), so
I can calculate it?

I tried using Format(mid(TXTDate,5,2)&"/"mid(7,2)&"/"mid(3,2),"mm/dd/yy"). It
works, but I still can't use it as a date, so I can't put a parameter on it.

Thanks in advance

KF
 
G

Guest

Surround your Formatting work with the CDate() function. One problem with
CDate is that it will error out if given a Null value or something that can
not be evaluated as a date. I also highly recommend that your format has yyyy
as Access might guess wrong if the years far in the past or future like 1929
or 2033.
 
F

fredg

Hi,

Is anybody can tell me, how to convert from text data to Date?. I have a
table imported from Text file, text file only recognize text & number only.
One of the field (we call: TxtDate) they have is '20070515" (yyyymmdd) has to
be in date format. How can I convert it to our regular format (mm/dd/yy), so
I can calculate it?

I tried using Format(mid(TXTDate,5,2)&"/"mid(7,2)&"/"mid(3,2),"mm/dd/yy"). It
works, but I still can't use it as a date, so I can't put a parameter on it.

Thanks in advance

KF

NewDate:Format(DateSerial(Left([FieldName],4),Mid(FieldName],5,2),Right(FieldName],2)),"mm/dd/yy")
 
J

John Spencer

IIF(
IsDate(Format(txtDate,"@@@@\/@@\/@@")),DateValue(Format(txtDate,"@@@@\/@@\/@@")),Null)

Format function always returns a string. So if you have a string that is
valid as a date, you can convert it with CDate or DateValue.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
K

KimTong via AccessMonster.com

It works find using CDate or DateValue function, I can calculate that field.
But I still can't put a criteria, such as: >#6/1/07# on that field. Is anyone
can figure it out why?. Thanks...

KF
[quoted text clipped - 10 lines]

NewDate:Format(DateSerial(Left([FieldName],4),Mid(FieldName],5,2),Right(FieldName],2)),"mm/dd/yy")
 
K

KimTong via AccessMonster.com

It works find using CDate or DateValue function, I can calculate that field.
But I still can't put a criteria, such as: >#6/1/07# on that field. Is anyone
can figure it out why?. Thanks...

KF


John said:
IIF(
IsDate(Format(txtDate,"@@@@\/@@\/@@")),DateValue(Format(txtDate,"@@@@\/@@\/@@")),Null)

Format function always returns a string. So if you have a string that is
valid as a date, you can convert it with CDate or DateValue.
[quoted text clipped - 15 lines]
 
J

John Spencer

You cannot use FORMAT if you want a field (column) that is a datetime field.
The format function converts the value into a string.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

KimTong via AccessMonster.com said:
It works find using CDate or DateValue function, I can calculate that
field.
But I still can't put a criteria, such as: >#6/1/07# on that field. Is
anyone
can figure it out why?. Thanks...

KF
[quoted text clipped - 10 lines]

NewDate:Format(DateSerial(Left([FieldName],4),Mid(FieldName],5,2),Right(FieldName],2)),"mm/dd/yy")
 
J

John Spencer

In a query #6/1/2007# is June 1st of 2007. Is that what you expect it to
be?

Perhaps you could post the SQL of the Query that doesn't work.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

KimTong via AccessMonster.com said:
It works find using CDate or DateValue function, I can calculate that
field.
But I still can't put a criteria, such as: >#6/1/07# on that field. Is
anyone
can figure it out why?. Thanks...

KF


John said:
IIF(
IsDate(Format(txtDate,"@@@@\/@@\/@@")),DateValue(Format(txtDate,"@@@@\/@@\/@@")),Null)

Format function always returns a string. So if you have a string that is
valid as a date, you can convert it with CDate or DateValue.
[quoted text clipped - 15 lines]
 
K

KimTong via AccessMonster.com

Yes. 6/1/07 means June 1st 2007, so I can't put any criteria on that field.
But I can calculate it, such as NewDate+10 etc...


John said:
In a query #6/1/2007# is June 1st of 2007. Is that what you expect it to
be?

Perhaps you could post the SQL of the Query that doesn't work.
It works find using CDate or DateValue function, I can calculate that
field.
[quoted text clipped - 15 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dateadd on month 6
Convert Date 1
Date format 1
converting date to text 2
Date comparison 2
Excel date format YYYYMMDD 1
Append/Update Query 6
Date Calculation 1

Top