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

[Paul Wolstenholme]

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