Stuart McLachlan
stuart at lexacorp.com.pg
Sat Apr 23 18:05:22 CDT 2011
Time() is a new type in SQL Server 2008. Are you sure the other field is DateTime and not DateTime2 (also new in 2008)? Basically, you are screwed. MS in its wisdom decided to pass Time() and DateTime2 to "down-level clients" as strings, rather than as the numerics which are used to store them, so you will have to parse then back to compatible Access datatypes yourself. See http://msdn.microsoft.com/en-us/library/ms180878%28v=SQL.100%29.aspx#BackwardCom patibilityforDownlevelClients "Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types added in SQL Server 2008. The following table shows the type mapping between an up-level instance of SQL Server 2008 and down-level clients." -- Stuart On 23 Apr 2011 at 18:16, jwcolby wrote: > The SQL Server fields are time(0) and DateTime. It is picked up by > Access as a string, IOW the field in design view of the linked table > is a string. That probably happens because Access does not understand > the miliseconds in the time, and AFAIK Access doesn't have a time > field, only datetime. > > The issues are many but small, including weird displays of the values, > necessity to convert to a date - cdate() - to do compares with other > times (it converts to date just fine) and finally, I was trying to use > the conditional display to color a date where it was <= date() + 180 > (for example) and it just doesn't work, the colors do happen but there > is no rhyme or reason to the colors. > > Things like that. > > So Access thinks it is a string, SQL Server thinks it is a date or > time(0) and I am constantly fiddling with it to make it right. > > John W. Colby > www.ColbyConsulting.com > > On 4/23/2011 5:28 PM, Stuart McLachlan wrote: > > What sort of issues? > > > > Is the SQL Server field a datetime or a smalldatetime? > > Using ODBC? What Client? > > > > AFAIK, Access doesn't have a "Date" field type, only a Date/Time > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >