Access 2007 database migrated to SQL Sever 2008 - Pop up calendar problem

[Richie27]

Hi all,

I have migrated an access 2007 database to sql server and have a small problem with the date field. Well, two small problems.

After migration Access displays the date like this: 1994-12-10 00:00:00 and I want it to display like this 10-12-1994.

The second problem is that the pop up calendar feature doesn't work anymore and I'd really like it to for usability and what not.

If anyone has any brain waves I would be eternally grateful.



Thanks,
Rich.

[Richie27]

Well,

I have since found this http://www.access-programmers.co.uk/...d.php?t=213963 and it works pretty nicely for storing and formatting the dates.

The only thing is - when you close the form and reopen it the dates are again like this: 1994-12-10 00:00:00.

Would there be a way to format the dates as was mentioned in my former post?

[Richie27]

Well, I have a big problem now. While the above solution works for a form and a subform - it falls down when there is a subform inside of a subform.......any simpler solution would be much appreciated..............

[Richie27]

Very simple fix actually, all it is is that when you migrate your access db to sql server your date field is converted into a format that access does not recognise, which is why your date picker doesn't work.

If you look in your table properties (afer migration) you will see that access now thinks it is a text field - hence - no date picker.

All you need to do is change your date fields in sql server to SMALL DATE TIME. Access will now recognise your fields as date fields and the date picker will work.

Happy date picking!!!!!!!!!