Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unhappy Unanswered: Access 2000: Converting a 12 digit string into date/time data type

    I was wondering if anyone could give me alittle help on a problem I am having with Microsoft Access 2000.

    I have a column in a table populated with 12 digit strings.
    (For example: 040705151832)
    These number represent both a concatenated date and time.
    YYMMDDHHmmss

    I would like to set this string into a 'General Date' to look something like this:

    MM/DD/YY HH:mm:ss
    07/05/04 15:18:32

    Now, the problem is that every time I go into Design view and set the Data type to Date/Time to format 'General Date', Access says the records were deleted for that column. Does anyone have any ideas on how I could accomplish this conversion from string to date/time?

    _David

    ps. I am a real notice on Microsoft Access, so any help would be greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    with a small amount of coding, you could write a function that performs the conversion for you. Is this a one time conversion, or something you'll need to do over and over?

    try this: create a new form and place this in the code for the click event of a button:
    Code:
    Sub Command1_Click()
    
        Call ConvertDate
    
    End Sub
    
    Sub ConvertDate()
    
        Dim strS() As String
        Dim iC As Integer
        Dim dtVal As Date
        Dim rsDat As DAO.Recordset
        
        Set rsDat = CurrentDb.OpenRecordset("Select [StringDateField], [NewDateField] From YourTable")
        While Not rsDat.EOF
            ' Assume the string is in the format: YYMMDDHHmmss
            ' Pick apart the string into seperate pieces
            ReDim strS(7)
            For iC = 1 To 6
                strS(iC) = Mid$(rsDat(0) & "            ", (iC * 2 - 1), 2)
            Next
            ' Put it back together in a recognizable date format3
            strS(7) = strS(2) & "/" & strS(3) & "/" & strS(1) & " " & strS(4) & ":" & strS(5) & ":" & strS(6)
            ' Write is back to the table in the new field
            rsDat.Edit
            rsDat(1) = dateValue(strS(7))
            rsDat.Update
        Wend
        Set rsDat = Nothing
    End Sub
    have fun

  3. #3
    Join Date
    Jul 2004
    Posts
    4

    Red face alittle help

    tcace,

    Greetings tcace, thanks for the help thus far. In reply to our question I intend import the data from a text file, which has this undesirable string format. I would like to be able to import all that data so that it is in a general date/time format. I need to repeat this for the other imports I do. Will this function accomplish this? Could you give more detailed instructions on setting up this function, I have never set a function before. Thank you for your help and I appreciate all the help you have given me so far.

    _David

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This will work all-purpose:
    Code:
    Public Function LongStrToDate(StrIn As String) As Date
    LongStrToDate = Left$(StrIn, 2) & "/" & Mid$(StrIn, 3, 2) & "/" & Mid$(StrIn, 5, 2) & _
        " " & Mid$(StrIn, 7, 2) & ":" & Mid$(StrIn, 9, 2) & ":" & Right$(StrIn, 2)
    End Function
    Then after you import your data, run an update statement:

    UPDATE yourTable
    SET yourDateField = LongStrToDate(yourDateField)

    Edit: Oops!! you're not in the US... recode for your date standard:
    Code:
    Public Function LongStrToDate(StrIn As String) As Date
    LongStrToDate = Mid$(StrIn, 3, 2) & "/" & Mid$(StrIn, 5, 2) & "/" & Left$(StrIn, 2) & _
        " " & Mid$(StrIn, 7, 2) & ":" & Mid$(StrIn, 9, 2) & ":" & Right$(StrIn, 2)
    End Function
    Last edited by Teddy; 07-15-04 at 14:48.

  5. #5
    Join Date
    Jul 2004
    Posts
    4

    Thumbs up Thanks

    Thank you everyone for your help, I think I found that I have been looking for.

Posting Permissions

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