Trying to append records to a SharePoint list. Getting "set 1 field(s) to Null due to a type conversion failure." Can query redefine type? (1 Viewer)

[diamondDog]

I am rather novice, so please forgive any obliviousness. I am aware that you can directly link a SharePoint list to Access so user input in an Access form directly updates/adds data to the connected SharePoint list, but there is a lot more that needs to be worked through in my Acccess Databse before the data finds a home on SharePoint (and I don't want to maintain two disparate sets of data before actions are resolved in Access).

1) Is it safe to assume that the "set 1 field(s) to Null due to a type conversion failure" query error is simply due to something along the lines of my Access query trying to append a field that is set to text format whereas the SP list has the destination field set as a Number type?

2) I noticed that the format type settings of columns across Access and SharePoint aren't identically labeled. Are there any tips to help ensure compatibility when appending records from Access to SharePoint list table?

3) One of the whole points of a query is to update data, so is it possible to change the data format directly inside the query instead of changing SharePoint list column types or changing source Access field Data types? I am looking at the Design View of my Access append query and I am not sure where I would input format changes to ensure destination SharePoint compatibility but it seems like something that would be possible.

Any other good-to-know tips when trying to troubleshoot this query error would be greatly appreciated. Thanks!

 

[theDBguy]

Hi. I think the fundamental error in this logic is by mixing format with data type. The two are completely different entities when it comes to the data. Data type is important to the computer, that's where you would/could get a type mismatch error. Formatting is only relevant to the users when displaying the data, so it is easier to read - but it doesn't change their data type, just the way they look/appear.

 

[GPGeorge]

I am rather novice, so please forgive any obliviousness. I am aware that you can directly link a SharePoint list to Access so user input in an Access form directly updates/adds data to the connected SharePoint list, but there is a lot more that needs to be worked through in my Acccess Databse before the data finds a home on SharePoint (and I don't want to maintain two disparate sets of data before actions are resolved in Access).

1) Is it safe to assume that the "set 1 field(s) to Null due to a type conversion failure" query error is simply due to something along the lines of my Access query trying to append a field that is set to text format whereas the SP list has the destination field set as a Number type?

2) I noticed that the format type settings of columns across Access and SharePoint aren't identically labeled. Are there any tips to help ensure compatibility when appending records from Access to SharePoint list table?

3) One of the whole points of a query is to update data, so is it possible to change the data format directly inside the query instead of changing SharePoint list column types or changing source Access field Data types? I am looking at the Design View of my Access append query and I am not sure where I would input format changes to ensure destination SharePoint compatibility but it seems like something that would be possible.

Any other good-to-know tips when trying to troubleshoot this query error would be greatly appreciated. Thanks!

Responses to individual questions.

1) No, it is not safe to assume. Not in database development. The error message is telling you that the value in one or more records in one field is not compatible with the destination datatype. It could be a text value, but it could also be a date value. Don't assume, review the data and find the problem values and then figure out what caused the problem.

2) Format is not the same as datatype. Format means "take this value and apply some layout to it so that it appears to the user in a specific way." It does NOT mean, "Change the datatype of this value." As an obvious example, using US date convention, these are all the same value, and more importantly, stored in Access as the same datatype:

October 25, 2024
10/25/2024
the 10th of October, 2024
10 Oct 2024

And on and on.

Those are all the result of applying different formats to a single value of one datatype.

3) Yes, but you don't change the format (as noted above, that's irrelevant). You can convert one datatype to another, using CDate(), CLng(), CStr() and so on. That works if the value being converted can be successfully rendered in the target datatype. For example, a string such as "10-25-2024" can be converted to a date using CDate( 10-25-2024) because that is recognizable as a month, day and year (again, in US date format).

In the case you cite, a string of digits that needs to be converted to a number, it can be done using the appropriate conversion function, CLng(), CDbl(), and so on. However, a string of mixed digits and alpha characters would not be handled properly.

It's all down to the actual data you have and how thoroughly it's been analyzed and characterized before the append process.