Convert Text to Date

  • Thread starter Thread starter Rita Brasher
  • Start date Start date
R

Rita Brasher

I have asked this before and have lost the answer and all instances in
which I've actually used the function, so have to ask again. Apologize
for the repetition....


I have a text field representative of the date in the format of
"yyyymmdd". I know Oracle lets you do a "to_date" function, but Access
doesn't recognize that function. What is the function/process by which
I can convert the text field to a date field?

Thanks, in advance, for any help you can provide!

Rita Brasher
Project Engineer
IPEG Int'l MIS
FedEx Express
 
I have asked this before and have lost the answer and all instances in
which I've actually used the function, so have to ask again. Apologize
for the repetition....

I have a text field representative of the date in the format of
"yyyymmdd". I know Oracle lets you do a "to_date" function, but Access
doesn't recognize that function. What is the function/process by which
I can convert the text field to a date field?

Thanks, in advance, for any help you can provide!

Rita Brasher
Project Engineer
IPEG Int'l MIS
FedEx Express
Click to expand...

As long as the Text date is always 8 characters, in Year month day
order:

DateField =
DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2))
 
EXCELLENTE!!! Thanks so much!


I have asked this before and have lost the answer and all instances in
which I've actually used the function, so have to ask again. Apologize
for the repetition....

I have a text field representative of the date in the format of
"yyyymmdd". I know Oracle lets you do a "to_date" function, but Access
doesn't recognize that function. What is the function/process by which
I can convert the text field to a date field?

Thanks, in advance, for any help you can provide!

Rita Brasher
Project Engineer
IPEG Int'l MIS
FedEx Express
Click to expand...

As long as the Text date is always 8 characters, in Year month day
order:

DateField =
DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2))
Click to expand...
 
The basic expression that I use is
CDate(Format([TextField],"@@@@-@@-@@"))

Of course I usually use an expression in a query to handle nulls and bad data
so that I avoid errors.

IIF(IsDate(Format([TextField],"@@@@-@@-@@")),CDate(Format([TextField],"@@@@-@@-@@")),Null)

Use the DateSerial function can be a better option if the date is in some
other format such as ddmmyyyy or mmddyyyy. The reason for that is the
confusion that arises when as to what format the date is in when the
conversion takes place. I'm in the US and all my date literals use the US
mmddyyyy format, so
CDATE(Format([TextField],"@@-@@-@@@@"))
works well for me when the date string is in mmddyyyy format.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have asked this before and have lost the answer and all instances in
which I've actually used the function, so have to ask again. Apologize
for the repetition....

I have a text field representative of the date in the format of
"yyyymmdd". I know Oracle lets you do a "to_date" function, but Access
doesn't recognize that function. What is the function/process by which
I can convert the text field to a date field?

Thanks, in advance, for any help you can provide!

Rita Brasher
Project Engineer
IPEG Int'l MIS
FedEx Express
Click to expand...

As long as the Text date is always 8 characters, in Year month day
order:

DateField =
DateSerial(Left([TextDate],4),Mid([TextDate],5,2),Right([TextDate],2))
Click to expand...
 
Rita said:
I have a text field representative of the date in the format of
"yyyymmdd". I know Oracle lets you do a "to_date" function, but Access
doesn't recognize that function. What is the function/process by which
I can convert the text field to a date field?
Click to expand...


You could create your own function to do that:

Public Function ConvertToDate(x)
If IsNull(x) Then
ConvertToDate = Null
Else
ConvertToDate = DateSerial(Left(x,4), Mid(x, 3,2),
Right(x,2))
End If
End Function
 
Back
Top