Convert Text to Date Field

[sjck]

I am having issues converting a text field to a date. I am using a table that is linked to a text file with the dates in the following format: I am using Access 2003.

DTS
7/26/2007 7:06:05.000 AM
7/26/2007 7:06:05.000 AM
8/1/2007 7:06:28.000 AM
8/6/2007 7:05:27.000 AM

I want the result in my query to just have the date (dropping the time)
7/26/2007
7/26/2007
8/1/2007
8/6/2007

Because my incoming date is variable in length; I was not able to use the Left function.

I have tried change the format of the field using CDate(DTS) and receive #Error in the output.

I have also tried to format the date Format([WMHS_INIT_RTE_DTM],"mm/dd/yyyy")and my output does not change: 7/26/2007 7:06:05.000 AM

Below is a query of the options that I have tried:
SELECT Test.WMHS_INIT_RTE_DTM AS DTS, CDate([WMHS_INIT_RTE_DTM]) AS DTSCDATE, Format([WMHS_INIT_RTE_DTM],"mm/dd/yyyy") AS DTSFormat
FROM Test;

which produces the following output:

DTS
7/26/2007 7:06:05.000 AM
7/26/2007 7:06:05.000 AM
8/1/2007 7:06:28.000 AM
8/6/2007 7:05:27.000 AM

DTSCDATE
#Error
#Error
#Error
#Error

DTSFormat
7/26/2007 7:06:05.000 AM
7/26/2007 7:06:05.000 AM
8/1/2007 7:06:28.000 AM
8/6/2007 7:05:27.000 AM

I have also tried importing the table with text data type (same results), linking to the table using date/time data format (results #Num in field)and importing using date/time data type (results Type Conversion Failure)

Thank you for your help. I am not sure what to do now.

 

[SkipVought]

Hi,

Its ALREADY a date...

INT([WMHS_INIT_RTE_DTM]) AS DTSCDATE

BTW your THIRD Format value is a STRING and not a date.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sjck]

Thank you for your reply; but when I add the code I still get the #Error

Code:

SELECT Test.WMHS_INIT_RTE_DTM AS DTS, INT([WMHS_INIT_RTE_DTM]) AS DTSCDATE
FROM Test;

DTS
7/26/2007 7:06:05.000 AM
7/26/2007 7:06:05.000 AM
8/1/2007 7:06:28.000 AM
8/6/2007 7:05:27.000 AM

DTSCDATE
#Error
#Error
#Error
#Error

I am trying to just get the date portion of the field separated from the time, for this query it does not matter if date value or a string.

 

[SkipVought]

DateValue([WMHS_INIT_RTE_DTM]) AS DTSCDATE

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sjck]

I am still receiving the same results

SELECT Test.WMHS_INIT_RTE_DTM AS DTS, DateValue([WMHS_INIT_RTE_DTM]) AS DTSCDATE
FROM Test;

DTS
7/26/2007 7:06:05.000 AM
7/26/2007 7:06:05.000 AM
8/1/2007 7:06:28.000 AM
8/6/2007 7:05:27.000 AM

DTSCDATE
#Error
#Error
#Error
#Error

I have tried rebooting, relinking, reimporting the table as well.