text to date/time

[bscott]

I am creating a linked table into access using a .csv file. One of the columns is a date/time in the following format: "06-01-2010 06:10 AM ".

When I import it as Data Type Date/Time, Access shows "#Num!" in all of the fields (maybe because of the space at the very end?). Because of this I have imported it as text to retain the data and would now like to convert it to Date/Time format.

In doing research for this I keep seeing posts that say to use CDate(), however being fairly new to Access I am not sure where or how to use CDate(), or if maybe I can resolve it when importing the CSV somehow. Any help would be greatly appreciated.

[jimatqsi]

Here's an example.

dim strDate as string
dim dtmDate as date

strDate = "06/10/2010"
dtmdate = Cdate(strdate)

The result of that is that you have a date defined field (dtmdate) containing data that started out in life as a text field.

Hope that helps.

Jim

[ADezii]

I am creating a linked table into access using a .csv file. One of the columns is a date/time in the following format: "06-01-2010 06:10 AM ".

When I import it as Data Type Date/Time, Access shows "#Num!" in all of the fields (maybe because of the space at the very end?). Because of this I have imported it as text to retain the data and would now like to convert it to Date/Time format.

In doing research for this I keep seeing posts that say to use CDate(), however being fairly new to Access I am not sure where or how to use CDate(), or if maybe I can resolve it when importing the CSV somehow. Any help would be greatly appreciated.

1. ASSUMPTIONS
   • Table Name = CSV Import
   • Field Name = Field1
2. Import the *.csv File making sure that the Date Field is Imported as 'TEXT'
3. Create an Update Query that will remove all the Leading and Trailing Spaces from the Field (Field1)
   

   UPDATE [CSV Import] SET [CSV Import].Field1 = Trim([Field1]);

4. Go into the Table's (CSV Import) Design View and now change its Data Type to DATE/TIME

[NeoPa]

I found that CDate() worked quite well with the data you supplied. Even with the trailing space. I'm from England so my default date format doesn't even use "-" at all. I think this means that my findings would be consistent globally.

There is still the issue of changing the type and the data at different times. I expect ADezii's solution will work for you mind you (He's pretty experienced after all).

[bscott]

Great, thank you all for your much appreciated help.