I have a Microsoft Access 2010 Frontend app that is connected to a local copy of SQL Server 2012 Express. There seems to be an incompatibility with the date data type in SQL Server. The SQL db stores its dates in this format: yyyy-mm-dd . There is a Date of Birth field (DoB) in one of the SQL tables and the data type is set as 'date'. I realised that Access is confused with this field.
First, I designed a form to display the date in [DoB] in the format: dd-mmm-yyyy. I did this by setting the format property of the [DoB] field in the form's design view. But Access ignores this and still displays the date as yyyy-mm-dd.
I then decided to test the date in [DoB] against the present date [txtPresentDate] to see which was greater. As you'll see in the attached images, Access said the date in [DoB] was greater than present date even though the date in [txtPresentDate] is clearly greater than that of [DoB].
I also specified (in the form's design view) that Access should display the date picker control whenever the [DoB] field is clicked. It also ignored this. I then set a Macro to display the date picker whenever the [DoB] field is clicked. I then got a Macro Error 2046.
The Access 2010 Error Numbers & Description PDF describes Error 2046 as :
The command or action '|1' isn't available now.@* You may be in a read-only database or an unconverted database from an earlier version of Microsoft Access. * The type of object the action applies to isn't currently selected or isn't in the active view.@U
How do I remedy this?