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

[Gustav Brock]

Hi Paul

Access doesn't work very well with DateTime2, even though the newer versions have an option under settings to be "compatible" - but that does little, as DateTime2 in VBA is a no-no and probably ever will.

So, modify the field to be of data type DateTime (and your query will work), or - as you have found out - read the dates back and forth as text, which is how the ODBC driver operates for DateTime2:

INSERT INTO 
    Buffer_qL110MaltAnalysisReadoutTemp 
    ( nBufferEntry, LastExKiln ) 
SELECT 
    T.nBufferEntry,
    Format(T.LastExKiln, "yyyy-mm-dd hh:nn:ss")
FROM 
    Buffer_qL110MaltAnalysisReadout As T;

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af Paul Wolstenholme
Sendt: 29. juni 2022 04:43
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [AccessD] Can't append all the records in the append query - set 1 field(s) to null due to type conversion failure

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