Format Date yyyymmdd to mm/dd/yyyy

[rxm01l]

The Date Field from UNIX after I extract, it looked like this 20130305. I would like to change it or Format it to look like this 03/05/2013. Is there an easy way for me to format this date field in Access? I'd like to do this on a Form.. Thanks in advace..

 

[mdlueck]

Here you go...

Convert date format from YYYY-MM-DD to DD/MM/YYYY
http://www.access-programmers.co.uk/forums/showthread.php?t=230590#post1176680

 

[bob fitz]

MyDate: Format(Left([UnixDate],4) & "/" & Mid([Unixdate],5,2) & "/" & Right([unixdate],2),"mm/dd/yyyy")

I think you could use something like this in forms Record Source query.

 

[spikepl]

Both of the above suggestions work OK, but neither is strictly correct. The tolerance of Access with regard to data types never ceases to amaze me.

 

[bob fitz]

spikepl

Can you please explain why the suggestion I made is not "strictly correct"?
What would be the correct way?

 

[spikepl]

Your solution relies on an implicit conversion of a string to a date, namely from "yyyy/mm/dd" to a date variable, which then, in turn, is formatted by the Format-function according to the format specification supplied in the call.. The date inside the string is unambiguous, and Access accepts this, so everything goes well. If the more common ambiguous format for date was used in the string, we would be back to the usual US/non-US date problems stemming from an implicit conversion (when regional settings are not US).

 

[bob fitz]

Your solution relies on an implicit conversion of a string to a date, namely from "yyyy/mm/dd" to a date variable, which then, in turn, is formatted by the Format-function according to the format specification supplied in the call.. The date inside the string is unambiguous, and Access accepts this, so everything goes well. If the more common ambiguous format for date was used in the string, we would be back to the usual US/non-US date problems stemming from an implicit conversion (when regional settings are not US).

Thank you for the explanation.
Can you give us a "correct" solution.

 

[Brianwarnock]

And I always used DateSerial as I wanted the result to be according to my regional settings.
Please critic.

Brian

Edit actually that is not quite true, if creating a date when US format required then I would use CDate

 

[rxm01l]

Thank You everyone so much For taking the time to answer my post. I will try to use all the recomendations that is suggested to me and determine which work best for my user..Thank You..