Text to date

[brucewalker]

I have a table field that is set to text, but contains a date in the mm-dd-yy format. How can I change this to a short date format? Thanks.

 

[pr2-eugin]

You have to create a new field on the table for now call it TempDateField with Type Date/Time and then save the table. Create an UPDATE Query, something like..

UPDATE [COLOR=Blue]yourTableName[/COLOR]  SET TempDateField = DateSerial(Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 7, 2), Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 1, 2), Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 4, 2))

However you can also create a small function in a Module and use the method to be called from your Query too...

Public Function changeToDate(strDate As String) As Date     
    'Considering that the Date String you obtain is of this format 12-12-12 (mm-dd-yy)         
    changeToDate = DateSerial(Mid(strDate, 7, 2), Mid(strDate, 1, 2), Mid(strDate, 4, 2)) 
End Function

So in your Query you can use it as..

UPDATE [COLOR=Blue]yourTableName[/COLOR]  SET TempDateField = changeToDate([COLOR=Blue][yourCurrentStringDateField][/COLOR])

Blue bits need to change.

 

[brucewalker]

Paul,
Thank you. I have a follow up question. If I create an update query do I need only the two fields, being the one with the mm-dd-yy format and the tempdatefield? Also do I put the code into the "update to" line of the query in the tempdate field? Thanks.

 

[pr2-eugin]

Yes.. but to avoid confusion.. Just copy the following code into SQL view of the Query Design window..

UPDATE yourTableName  SET TempDateField = DateSerial(Mid([yourCurrentStringDateField], 7, 2), Mid([yourCurrentStringDateField], 1, 2), Mid([yourCurrentStringDateField], 4, 2))

by replacing [yourCurrentStringDateField] with the name of the Actual string field.. Once the Data is updated you can delete the field...

 

[brucewalker]

Below is what I pasted into into the SQL view of the query.

"UPDATE Copy Of Parts shipped 2001 SET TempDateField"=DateSerial(Mid([PRCDTE],7,2),Mid([PRCDTE],1,2),Mid([PRCDTE],4,2))

I suspoect I have done something wrong because when I try to back to design view I get the following error;

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

 

[pr2-eugin]

Okay two things..
1. Have you created the field TempDateField in the table before running the Query?
2. If you have Spaces in your Table name you have to enclose them within square brackets.. so..

UPDATE [COLOR=Red][B][[/B][/COLOR]Copy Of Parts shipped 2001[COLOR=Red][B]][/B][/COLOR] SET TempDateField = DateSerial(Mid([yourCurrentStringDateField], 7, 2), Mid([yourCurrentStringDateField], 1, 2), Mid([yourCurrentStringDateField], 4, 2))

 

[brucewalker]

Paul,
Thank you for your assistance. I really apprectaite it.

Yes I have created that field in the table with the proper data type. When I add the square brackets as below, I still get the same error. Does the TempDateField name have to have brackets as well?

"UPDATE [Copy Of Parts shipped 2001] SET TempDateField"=DateSerial(Mid([PRCDTE],7,2),Mid([PRCDTE],1,2),Mid([PRCDTE],4,2))

 

[pr2-eugin]

Whys is there Double Quotes surrounding your Query??

"UPDATE [Copy Of Parts shipped 2001] SET TempDateField"=DateSerial(Mid([PRCDTE],7,2),Mid([PRCDTE],1,2),Mid([PRCDTE],4,2))

 

[brucewalker]

Paul,
When I pasted it into the SQL view it put them there. I did remove them and wit the brackets around the table name the query ran.

I did find an error on my part though in the date format. The date is actually format as dd-mm-yy not mm-dd-yy as I originally communicated. My apologies for that. Can a query still work with this other format? Thanks again for your patience ands assistance.

 

[pr2-eugin]

Paul,
When I pasted it into the SQL view it put them there. I did remove them and wit the brackets around the table name the query ran.

Okay let us do it the other way then, go to the Query design window.. Select the Update Query.. Then in the Update To field put this..

DateSerial(Mid([yourCurrentStringDateField], 7, 2), Mid([yourCurrentStringDateField], 4, 2), Mid([yourCurrentStringDateField], 1, 2))

In the field Name Select the TempDateField from the drop down list.

I did find an error on my part though in the date format. The date is actually format as dd-mm-yy not mm-dd-yy as I originally communicated. My apologies for that. Can a query still work with this other format? Thanks again for your patience ands assistance.

Thats fine bruce.. I have changed the code above according to the dd-mm-yy format..

 

[brucewalker]

Paul,
It worked perfectly. Thanks very much for your help.

 

[pr2-eugin]

You are most welcome.. Glad to help..