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

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


"Chris" wrote:

> 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
>
>
>

-- 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,"@@@@\/@@\/@@"))



"Chris" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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
>