Sign in
 
HomeOnline20132010Other VersionsRelated ProductsLibraryForumsGallery
TechCenter >
> 
> 
Updating date fields on SharePoint List from Access Update Query

Answered Updating date fields on SharePoint List from Access Update Query

  • Thursday, July 05, 2012 6:06 PM
     
     
    Sign In to Vote
    0

    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? 

     

All Replies

  • Friday, July 06, 2012 11:18 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi ,

    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 .

    1. Create an external source to SharePoint 2010 list .
    2. Check ‘Link to the date source by creating a linked table  ’ .
    3. In this way ,you can update the column as you want .
    4. 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 .

    Thanks,

    Entan Ming

    Entan Ming

    TechNet Community Support

     
  • Friday, July 06, 2012 7:49 PM
     
     
    Sign In to Vote
    0

    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 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi ,

    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 ) .

    Thanks,

    Entan Ming

    Entan Ming

    TechNet Community Support

     
  • Friday, July 13, 2012 12:59 PM
     
     Answered
    Sign In to Vote
    0

    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:

    http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/8a7baf3b-5a3a-4631-82f9-b43d992155d1

    • Marked As Answer by Mary Mascari Friday, July 13, 2012 12:59 PM
    •