datatype conversion

[Chris]

Q - How do I convert a datatype text field (yyyyddmm) into a different table
under a date data type?

Story
I am receiving a large text document that has the date format as yyyyddmm.
I import all the data from the text file int a table and then I run a query
to only take the relavant information and put it into a table that makes
sense. The date currently goes into a table with the datatype as text but I
would like to put this into the proper table with a datatype as date format
dd/mm/yyyy.

I have tried this

strDate1 = DLookup("receiveddate", "tblHobsrefined")
strDay = Right(strDate1, 2)
strMonth = Mid(strDate1, 5, 2)
strYear = Left(strDate1, 4)

dteMaskeddate = "" & strDay & "/" & strMonth & "/" & strYear

strUpdate = " Update tblHobsRefined " & _
" Set ReceivedDate = " & quote & dteMaskeddate & quote
DoCmd.RunSQL strUpdate

 

[chris.nebinger]

A date is not stored as a text string in the database, it is actually
saved as a number. If your field is a text field, then you should
create a new field that is a date type. Then, you could:



strDate1 = DLookup("receiveddate", "tblHobsrefined")
strDay = Right(strDate1, 2)
strMonth = Mid(strDate1, 5, 2)
strYear = Left(strDate1, 4)

dteMaskeddate = DateSerial(strYear,strMonth,strDay)
strUpdate = " Update tblHobsRefined " & _
" Set NewReceivedDate = #" & dteMaskeddate & "#"
DoCmd.RunSQL strUpdate


Or, all through SQL:

Update tblHobsRefined set NewReceivedDate =
DateSerial(left([receiveddate]),4),Mid([receiveddate],5,2),right([receiveddate],2))



Chris Nebinger