date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 14 Aug 2006 16:32 GMT

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

Reply to this Message

jmonty - 14 Aug 2006 17:07 GMT

First of all you are not using any criteria in your DLookup which means it
will always use the value of the first record it finds. You may want to
revise - unless you meant to do that. Anyway...

strDate1 = DLookup("ReceivedDate", "tblHobsrefined")
Docmd.RunSQL "UPDATE tblHobsRefined Set ReceivedDate = #" & _
Mid(strDate1,5,2) & "/" & Right(strDate1,2) & "/" & Left(strDate1,4) & "#"

here I am assuming that your ReceivedDate field is of data type = Date/Time.
You need to qualify dates in SQL strings inbetween # signs.
Example: #08/14/2006#

Now the only problem you will run into is if the date field is empty or a
Null. If it is, your update will error. You may want to nest the IIF() and/or
Nz() and Now() or Date() functions to prevent any errors. See Access help for
more info on these functions.

LOL.

jmonty

> Q - How do I convert a datatype text field (yyyyddmm) into a different table
> under a date data type?
[quoted text clipped - 19 lines]
> " Set ReceivedDate = " & quote & dteMaskeddate & quote
> DoCmd.RunSQL strUpdate

Reply to this Message

John Spencer - 14 Aug 2006 19:35 GMT

-- Add a new field to your table - make its data type Date
-- Run an update query that looks like the following:

Update tblHobsRefined
Set NewDate = CDate(Format(ReceivedDate,"@@@@\/@@\/@@"))
WHERE ReceivedDate is not null and
IsDate(Format(ReceivedDate,"@@@@\/@@\/@@"))

>Q - How do I convert a datatype text field (yyyyddmm) into a different
>table under a date data type?
[quoted text clipped - 19 lines]
> " Set ReceivedDate = " & quote & dteMaskeddate & quote
> DoCmd.RunSQL strUpdate

Reply to this Message

Chris - 15 Aug 2006 14:23 GMT

testing outlookexpress.

"Chris" <chris@abc.com> wrote in message news:...

Reply to this Message