Date_Time as text

[Guest]

I am pulling data into access and the date time field is stored as a text
field in this format, 200705031200. How do I do a date diff between 2 dates
stored as text? Can I convert them? Or.... ?

Thanks,
Sean

 

[fredg]

I am pulling data into access and the date time field is stored as a text
field in this format, 200705031200. How do I do a date diff between 2 dates
stored as text? Can I convert them? Or.... ?

Thanks,
Sean

I assume the above 20070503 represents May 3, 2007 with the 1200 being
a time value.

DateSerial(Left([Field1],4),Mid([Field1],5,2)Mid([Field1],7,2))

will return a date datatype value of 05/03/2007.

You can use it in DateDiff().

DifferenceateDiff("d",DateSerial(Left([Field1],4), Mid([Field1],5,2)
, Mid([Field1],7,2)), DateSerial(Left([Field2],4), Mid([Field2],5,2),
Mid([Field2],7,2)))

 

[John Spencer]

You should be able to convert the string to a datetime data type using the
following.
CDate(Format([TextField],"@@@@ @@ @@ @@@"))

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

 

[Guest]

How do I include the time? I am comparing transactions that can be done on
the same day but only hours apart as where others can be 36 hrs apart.

I am pulling data into access and the date time field is stored as a text
field in this format, 200705031200. How do I do a date diff between 2 dates
stored as text? Can I convert them? Or.... ?

Thanks,
Sean

I assume the above 20070503 represents May 3, 2007 with the 1200 being
a time value.

DateSerial(Left([Field1],4),Mid([Field1],5,2)Mid([Field1],7,2))

will return a date datatype value of 05/03/2007.

You can use it in DateDiff().

DifferenceateDiff("d",DateSerial(Left([Field1],4), Mid([Field1],5,2)
, Mid([Field1],7,2)), DateSerial(Left([Field2],4), Mid([Field2],5,2),
Mid([Field2],7,2)))

 

[Guest]

I get an invalid syntax error, any suggestions?

You should be able to convert the string to a datetime data type using the
following.
CDate(Format([TextField],"@@@@ @@ @@ @@@"))

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

I am pulling data into access and the date time field is stored as a text
field in this format, 200705031200. How do I do a date diff between 2
dates
stored as text? Can I convert them? Or.... ?

Thanks,
Sean

 

[John Spencer]

Is your text field ever Null? Is your text field ever anything other than
12 number characters? Is your textfield ever anything other than 12
characters long? Those would all generate an error when CDate attempted to
handle them and then the query might generate a too complex error.

You could try the following. IsDate returns true or false if the string can
be interpreted as a date. Then if IsDate believes the string can be
converted, CDate should not generate an error.

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

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

I get an invalid syntax error, any suggestions?

You should be able to convert the string to a datetime data type using
the
following.
CDate(Format([TextField],"@@@@ @@ @@ @@@"))

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

I am pulling data into access and the date time field is stored as a
text
field in this format, 200705031200. How do I do a date diff between 2
dates
stored as text? Can I convert them? Or.... ?

Thanks,
Sean

 

[Guest]

Forgot to include,

The field is never null and it is always 12 characters.

Is your text field ever Null? Is your text field ever anything other than
12 number characters? Is your textfield ever anything other than 12
characters long? Those would all generate an error when CDate attempted to
handle them and then the query might generate a too complex error.

You could try the following. IsDate returns true or false if the string can
be interpreted as a date. Then if IsDate believes the string can be
converted, CDate should not generate an error.

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

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

I get an invalid syntax error, any suggestions?

You should be able to convert the string to a datetime data type using
the
following.
CDate(Format([TextField],"@@@@ @@ @@ @@@"))

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

I am pulling data into access and the date time field is stored as a
text
field in this format, 200705031200. How do I do a date diff between 2
dates
stored as text? Can I convert them? Or.... ?

Thanks,
Sean