Date Conversion Question (1 Viewer)

[SaraMegan]

Hi everyone.

I'm working on a new project where I get all my data from an outside source imported as a .txt file. The Main table has already been created, my job is just basically to manipulate data, calculate some stuff, and basically just make it easy to understand.

The calculations I need to do are DateDiff, which I know how to do, except that my data comes in as a text field and it comes in this format: yyyymmdd (20021210).

So what I need to be able to do is somehow get this recognized as a date so that I can do a datediff... but I think changing the field's data type will mess up importing data, plus, I don't think it'll recognize the format and it'll just get confused anyway.

Not at all sure where to start here.... I've done a forum search, but haven't quite seen this problem... Any thoughts?

Thanks in advance!

--Sara

 

[Mile-O]

If you had another field - this time an empty date/time field to work with on importing I'd recommend the DateSerial command.


datDate = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))


where datDate would be your empty date/time field

and strDate is the imported date as a string

 

[SaraMegan]

Mile-O-Phile,

Thanks for the reply! That was quick!

Do you think you could tell me more about how that works? I'm still learning so I don't understnad where your numbers are coming from - where do I put the empty date/time fields? If I put them at the end of the table, would it not effect the importing of data?

Thank again.

--Sara

 

[Mile-O]

I'm nowhere near good myself, but I'd suggest that if you are importing into a table (I'm guessing you have a field called Date where the date (as a string i.e "20021210") is imported to.

So, if you create a sub to call after the import: a quick one may be:

Sub DateRemedy()

     Dim db as Database, rs as Recordset
     Dim intCounter as Integer, strDate as String

     Set db = CurrentDb
     Set rs = db.OpenRecordset("tblImportedData")

     rs.MoveFirst

     For intCounter = 1 to rs.Recordcount
          strDate = rs.Fields("Text Date")
          With rs
                .Edit
                .Fields("Fixed Date") = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)) 
                .Update
                .MoveNext
           End With
      Next intCounter

End Sub

It may not be the best solution, but it should work.

As to where you would put the Empty Date/Time field: in the table you are importing to, just klaeve it blnk whilst importing.

 

[DJN]

You could run an update query on the table after importing.

UPDATE tblTableName SET tblTableName.txtFieldName = DateSerial(Left([txtFieldName],4),Mid([txtFieldName],5,2),Right([txtFieldName],2));

Should do the trick. Just copy and paste the SQL into the SQL view of a query and run it. Make sure you have a copy of the table data first.


David

 

[SaraMegan]

Okay, Mile-O-Phile, I tried out your code and it worked beautifully! Is there a way to do it for five different fields all at once? If not, then perhaps DJN's method is the way to go?

DJN - haven't tried your method yet, but I'm just about to. Thanks for the response!

--Sara

 

[Mile-O]

Of course there is - just have the relevant amount of blank fields and at this bit:

With rs
                .Edit
                .Fields("Fixed Date") = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)) 
                .Update
                .MoveNext
           End With

you could just put as many in as you need:


remembering to dimension the strDate as an array this time: i.e Dim strDate(1 To 3) as String

With rs
                .Edit
                .Fields("Fixed Date1") = DateSerial(Left(strDate(1), 4), Mid(strDate(1), 5, 2), Right(strDate(1), 2)) 
                .Fields("Fixed Date2") = DateSerial(Left(strDate(2), 4), Mid(strDate(2), 5, 2), Right(strDate(2), 2)) 
                .Fields("Fixed Date3") = DateSerial(Left(strDate(3), 4), Mid(strDate(3), 5, 2), Right(strDate(3), 2)) 
                .Update
                .MoveNext
           End With

 

[SaraMegan]

Thanks, Mile-O-Phile. That's brilliant! Works great.

--Sara