Annoying date format problem 2

[jmc014]

Hi,

I'd like to share an issue that I have with a real annoying date format problem.
After upsizing a DB to SQLExpress 2014, all the date formats changed to: YYYY-MM-DD HH:MM:SS.

While the reasons are clear, the visual impact is not really friendly/what we are looking for.
The problem being that I'm having a "wonderful" time getting the date formats back to DD/MM/YYYY.

Typing dd/mm/yyyy in the format property has no effect!

I managed to change my date formats back to DD/MM/YYY using the below code:
Forms!MyForm!MyDate = Format(MyDate,"dd/mm/yyyy")

But the changes only lasted up until i click on any field in my form (then the formats go back to YYYY-MM-DD HH:MM:SS)

I've also tried: Forms!MyForm!MyDate.Controlsource = Format([MyDate],"dd/mm/yyyy"), and numerous other tests but all fall short.
I'm realy interested in knowing why the date format keeps returing back to the SQL property and if anything can be done to stop this.

It seems that SQLEXPRESS, through its OBDC connection, is dominating my project, but I cant understand why my settings are not holding after they have been changed.

I've never worked with an SQL backend before, so any feedback would be appreciated.
Thanks JMC,

 

[MajP]

Just choose 'Short Date' in the format property of the control. Should override any formatting at the table level.

 

[jmc014]

Thanks for the input, but that was the first thing that I tried.. same did not work.
Cheers,

 

[MajP]

See if this helps

https://social.msdn.microsoft.com/F...ew-and-setting-dates?forum=sqlservermigration

 

[jmc014]

Thanks for the link... I haven't got too much spare time at the moment, but this articule will surely help.
Would never have thought that the problem would have been with my SQL connection... But hay, I've see stranger!!

Again, thanks for your help.

JMC

 

[jmc014]

Just created a new ODBC connetion, this time with the SQL Server Native Client 11.0 driver, ticking the USE REGIONAL SETTINGS, and it worked!!
MajP... I'd give you a star (but I've already done that)..

Again, thank you very much for posting the articule.
JMC.

 

[SkipVought]

Jmc014, I'll be your proxy. MajP posts good stuff!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]