Thursday, July 05, 2012 6:06 PM
I'm trying to use an update query from Access 2010 to update a Date/Time field on a SharePoint 2010 list.
Every time I do, I get the dreaded "You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule" error.
I've tried converting the field's format to a date, to a string, to a variant... to no avail.
Interestingly, when I use a Iif expression (the HCOK column) to compare the fields, it's saying they're different:
The Expr1 column is a DateDiff comparing the days between the two. Which works fine.
Any hints on how I can update this field?
Friday, July 06, 2012 11:18 AMModerator
I understand that you have update query form Access 2010 to update a Date/Time field in SharePoint list .But you cannot save the changes .I have a test based on your description ,you can update the Date/Time column using an external source .
- Create an external source to SharePoint 2010 list .
- Check ‘Link to the date source by creating a linked table ’ .
- In this way ,you can update the column as you want .
- In Access ,the date/time column will only show dates .When add items to list ,the default time part will be 12 am .
You can also add query to update the list item .
TechNet Community Support
Friday, July 06, 2012 7:49 PM
That's what I was trying to do. I have a database with a local table, and then the SharePoint list as a linked table.
The date in the linked SharePoint list appears to be a Long Date (even though the table design says it's a Short Date). I've tried changing the format on my local table to Short Date, Long Date, and General Date, to no avail.
Tuesday, July 10, 2012 9:50 AMModerator
Can you describe the detailed steps about creating the access database ?
In my testing ,I have created a date column and a date and time column in SharePoint list .After adding the list as external data for the database ,the date column are showing as short and the date and time column are showing as general (This can be seen under Table Tools>Fields>Format ) .
You can edit the short and general date column both in Access .The general date time column will only have date part when add a new entry in Access ,You can manually type the time part to have a test whether you can save the changes (I can save the date part alone and when open the list in browser ,the time part is automatically set to 12:00 am ) .
TechNet Community Support
Friday, July 13, 2012 12:59 PM
I wound up figuring it out on my own. There were a few individual records that were causing problems. (I wish the error message had told me that).
I detailed what I did in this other thread:
- Marked As Answer by Mary Mascari Friday, July 13, 2012 12:59 PM