Converting Text to Date

[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

 

[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.

 

[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")

 

[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
..

 

[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

Hi,

[quoted text clipped - 10 lines]

KF

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

 

[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

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.

Hi,

[quoted text clipped - 15 lines]

KF

 

[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
..

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

Hi,

[quoted text clipped - 10 lines]

KF

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

 

[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
..

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

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.

Hi,

[quoted text clipped - 15 lines]

KF

 

[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...

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]