Convert date format

[dastr]

Hi all,

Do you know how to convert dd.mm.yyyy to DD/mm/YYYY (with slashes)?

Thanks in advance.

 

[pr2-eugin]

Does the field currently have Date/Time type or Text type??

* If it is in Date/Time then just change the format in design view from dd.mm.yyyy to dd/mm/yyyy or Short Date..
* If it is Text and you wish to change it to Date/Time then follow the procedure given below..

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, 4), Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 3, 2), Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 1, 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
    [COLOR=Green]'Considering that the Date String you obtain is of this format 1212 (dd.mm.yyyy)[/COLOR]
    changeToDate = DateSerial(Mid(strDate, 7, 4), Mid(strDate, 3, 2), Mid(strDate, 1, 2))
End Function

So in your Query you can use it as..

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

Blue bits need to change and later you can delete the old field that is String and rename the TempDateField to your old field name..