Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, I have a CSV file that I import into an access database. The date field in the CSV file is in the format of 20100622 for 06/22/2010. How do I convert this field into a date field so that it will read as a date field? Once it is converted to a date, the field will need to be linked to another date in a different table. So the field types both have to be a date.

    Thanks in advance for any assistance.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The mosts common approach is to use the DateSerial() function which lets you specify the year, month and day. To do that you could use Left([myTextDate],4) for the Year portion, Mid([myTextDate],5,2) for the Month, and Right([myTextDate],2) for the day of the month.
    Wendell

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    To expand upon Wendell's solution.
    I think you will have to import the CSV into a table with the date as it is as text.
    Then either calculate the date field from the textDate field in a Query using Wendell's method
    Or if you want the data as a real date in a table which might be a good idea if linking,
    then have another field in the import table called RealDate or whatever you want to call it, Data Type Date/Time.
    Once the csv is imported into this, then run an update query using Wendell's solution to Update the RealDate field from the textDate field
    Andrew

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Just to add, once it is in a database you can add a date field and run run a query to update the date field form the text field using a function like -

    Code:
    Public Function TextToDate(strDate As String) As Date
    Dim strYear As String
    Dim strMonth As String
    Dim strDay As String
    
        strYear = Left(strDate, 4)
        strMonth = Mid(strDate, 5, 2)
        strDay = Mid(strDate, 7, 2)
        strDate = strMonth& "/" & strDay& "/" & strYear
    
        TextToDate = CVDate(strDate)
    
    End Function
    Then use it in a query -
    Code:
    UPDATE TableName SET NewDateFieldName = TextToDate(oldFieldName)
    Now just remember that if you are trying to 'link' this date to a date in another table then you will need to ensure that the other date is just a date adn has no time component. Dates are really just numbers. Anything to the left of the decimal point is the day, to the right is the time.

    for example if I ask
    If dteDate1 = dteDate2 then
    it may not be true even if they display the same date.

    to strip off the time you can use Int()

    if int(dteDate1) = int(dteDate2) then
    will be true if theyare the same day but not the same time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •