Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Question Unanswered: converting text into date field format

    i am importing an old ibm text generated file into my database with multiple lines having each distinct fields where the date fields vary from line to line depending on 4 different criteria situations,,
    anyway i just import the whole thing into one table and run different queries,,however i must use just txt fields because the date field is ddmmmyyhhmm format ex,31MAY032100,
    now i want to extract each of the 4 groups into their own table and change the date fields into access format,,so i can run other queries depending on date values,,
    what would be the easiest way to do this?any ideas would be great,,

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: converting text into date field format

    Originally posted by 133100
    i am importing an old ibm text generated file into my database with multiple lines having each distinct fields where the date fields vary from line to line depending on 4 different criteria situations,,
    anyway i just import the whole thing into one table and run different queries,,however i must use just txt fields because the date field is ddmmmyyhhmm format ex,31MAY032100,
    now i want to extract each of the 4 groups into their own table and change the date fields into access format,,so i can run other queries depending on date values,,
    what would be the easiest way to do this?any ideas would be great,,
    Do you need to keep the time part of the field? If not... you could create a calculated field in your make table query... Something like this...

    fldNewDate: CDate(Left(fldOldDate, 7))

    I think that'll work... Let me know how it goes...

  3. #3
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    15
    Here is an example function that should work.

    Code:
    Function Run()
    Dim Result As Date
    Dim mDate As Date, mTime As Date
    Result = ConverStringToDate("31MAY032100", mDate, mTime)
    End Function
    
    Function ConverStringToDate(StrDate As String, Optional mDate As Date, Optional mTime As Date) As Date
    
    Dim DayVal As Integer
    Dim MonVal As Integer
    Dim MonStr As String
    Dim YearVal As Integer
    Dim HourVal As Integer
    Dim MinVal As Integer
    Dim TimeVal As Date
    
    DayVal = Left(StrDate, 2)
    MonStr = Mid(StrDate, 3, 3)
    YearVal = Mid(StrDate, 6, 2) + 2000
    mDate = CDate(DayVal & "/" & MonStr & "/" & YearVal)
    
    HourVal = Mid(StrDate, 8, 2)
    MinVal = Right(StrDate, 2)
    mTime = CDate(HourVal & ":" & MinVal)
    
    ConverStringToDate = mDate + mTime
    
    End Function
    The function will return the date and time combined in the variable Result. It also returns the seperated date and time values in the variables mDate and mTime.
    Hope this helps.

Posting Permissions

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