[AccessD] Can't append all the records in the append query - set 1 field(s) to null due to type conversion failure

[Stuart McLachlan]

The problem is that  while it displays to the nearest second, Access DateTime is a  double 
so the precision. is smaller than a second.

Try this:
? format$(now(),"d mmm yyyy hh:nn:ss.sssssss")

Two solutions:
1. Use DateTime2(3) or greater in your SQLServer table
2. Force the Date/Time to round to the nearest second as you are currrently doing with 
Format().

What driver are you using? I believe OBBC Driver for SQL Server v17 and above handles 
this more gracefullt.



On 29 Jun 2022 at 14:42, Paul Wolstenholme wrote:

> Hi,
> 
> I'm having a problem running the simplest of insert queries.  The
> problem has been replicated on Access 2010 with SQL Server 2014 and
> Access 365 with another SQL Server 2014 back end (running on Win7 and
> I think Win10 respectively).
> 
> The error message when running the query directly
> "...can't append all the records in the append query. ... set 1
> field(s) to null due to type conversion failure,..." The query
> produces no result - If I choose the option to run the query anyway,
> no record is created.
> 
> The query can be cut down to:
> 
> INSERT INTO Buffer_qL110MaltAnalysisReadoutTemp ( nBufferEntry,
> LastExKiln ) SELECT Buffer_qL110MaltAnalysisReadout.nBufferEntry,
> Buffer_qL110MaltAnalysisReadout.LastExKiln FROM
> Buffer_qL110MaltAnalysisReadout;
> 
> The first field is an integer primary key and the problem field is an
> Access table's date/time field which is being copied to a SQL Server
> datetime2(0) field (i.e. date/time with 1 second precision [followed
> by zero decimal places]).
> 
> The particular date/time entry in the table appears to be the problem.
>  If I copy the date/time field and paste it back to the source table,
> overwriting the value with the "same" value, the problem goes away. 
> There is no longer a type conversion failure.  Entering the value
> manually also makes the problem go away. If I copy the entire source
> table record (from a saved copy of the source table) and replace the
> source table record, the problem returns.  So there is nothing wrong
> with the part of the date/time entry that I can see and re-enter - the
> problem must be with aspects of the entry that are not visible to me. 
> The parts of the known problematic entries I can see are 5/06/2022
> 13:51:27 and 26/06/2022 14:55:21 (or in international format
> 2022-06-05 13:51:27 and 2022-06-26 14:55:21).
> 
> The original source table record would have been created by an Access
> query.
> 
> How on earth should one go about debugging this problem and avoiding
> it happening again?
> 
> As a work-around, replacing Buffer_qL110MaltAnalysisReadout.LastExKiln
> in the query with the following fixes the problem:
> Format([FirstExKiln],"yyyy-mm-dd hh:nn:ss") AS FirstExKilnD but not
> CDate([FirstExKiln]) AS FirstExKilnD It doesn't answer the question of
> what is the problem being worked around?
> 
> Thanks,
> Paul Wolstenholme
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>