Menu
I have a SQL Server 2008 database with an Access front-end. My problem is that Access does not recognise SQL Server's dates as they are in a different format.
SQL Server-s format is YYYY-MM-DD
Access' format is DD-MM-YYYY
When the date is displayed in a text-box, it is displayed as a string (without the little calendar icon next to it).
Is there anyway I can configure my Access front-end so that it recognises SQL Server's dates?
Cheers.
818
Access provides several predefined formats for date and time data. Open the table in Design View. In the upper section of the design grid, select the Date/Time field that you want to format. In the Field Properties section, click the arrow in the Format property box, and select a format from the drop-down list.
Access stores the Date/Time data type as a double-precision, floating-point number up to 15 decimal places. The integer part of the double-precision number represents the date. The decimal portion represents the time. Valid date values range from -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.).
You can use the Now function or Date function to have Access automatically fill in the date or time when a new record is added. Use the Now function to fill in the date and time, or the Date function to fill in just the date.
If you are storing the dates in SQL server as the data type “Date” or “Date2” try changing them to “DateTime” I had this problem linking data from SQL server 2008R2 to access 97, access did not see it as a date and treated it like text
72
The Microsoft SQL Server Migration Assistant for Access does move the dates over as a Date format. The problem the user has encountered is with a Driver. Microsoft has a new DLL that must be put on a client workstation (or server in the case of Citrix). Once that is done, all the dates in MS Access will work properly. My lastest experience with Access 2010 was exactly the same as Access 2003. It required a DLL. From there, investigate using a DNS-Less connection string.
45
I just ran in to this, thanks everyone for your input.
I'm also developing an Access UI for a SQL Server backend (Access 2010/SQL Server 2014) and just encountered this problem. The Date datatype takes 3 bytes of storage, and since I didn't need a time component, that's what I wanted. Personally I'm using SmallDateTime, it takes 4 bytes compared to DateTime's 8. There's also DateTime2 that takes 6-8 bytes.
I created a four field table using each of the date datatypes to experiment with input formats, I think SmallDateTime will do the trick for me.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
