Converted access be to sql, date problems

[zgray]

We converted our split database back end to sql and went in and changed our date datatypes from datetime2 to date. In the front end all our dates are displaying yyyy-mm-dd. I went into the forms and set the format property of the control to short date and it does nothing, I formatted the date on the query, format(date,"Short Date"), but it locks the field of course. How do I get it to display the dates as short date, mm-dd-yyyy, other than vba coding every date field. There has to be an easy way I am missing.

Also the date picker is gone. Any help on both of these would be awesome.

 

[mdlueck]

The way I deal with dates in Access / SQL BE DB is in the SQL BE DB they are stored in a datetime2(0) field type.

When Stored Procedures / Pass-Through SQL selects the dates out, then I format them to the display preferences. SQL examples of such:

CONVERT(varchar(10), [met].[toolpodate], 101) AS [toolpodate]

This takes the date as it is stored in SQL Server, and presents it in mm/dd/yyyy format.

So perhaps you may solve the troubles you are seeing by updating the SQL code to have the server return the date in the desired format.

To insure that Access / VBA / etc... did not tamper with the dates the SQL BE DB intended to communicate, CONVERT is instructed to output a 10 character length string.

 

[Galaxiom]

Put Short Date in the Format property of the controls where the date is displayed in the FE.