Results 1 to 11 of 11
  1. #1
    skbpillai is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2016
    Posts
    6

    Blank Date Field (Calculated) in SP shows as #Error in Linked MSAccess Table

    I have a list in SharePoint that I link in my MS Access Database. This list has a Calculated Field. It compares two date fields and updates a date value OR a blank. Now, when the records are sync'ed back in MS Access, all fields where it is left blank comes as #Error. Any pointers to mitigate this issue would be a great help.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if a date is blank, that will error any date calculations.
    if the calculation has a null in it , the result will be null
    if the calculation is using form fields, then you must use the textbox name, NOT the field name. (but sometimes they are the same)

  3. #3
    skbpillai is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2016
    Posts
    6
    Hi Ranman256,
    Thanks for your message. Let me explain a bit more in detail. We are talking about a list that has two dates. There is a 3rd column (calculated) that arrives at a date value based on the initial two fields. Please see screenshot below.
    Click image for larger version. 

Name:	FIN_PCEDate.png 
Views:	24 
Size:	4.8 KB 
ID:	23527

    =IF(Changed_PCEDate="",IF(PCEDate="","",PCEDate),C hanged_PCEDate)

    Now, if both PCEDate and Changed_PCEDate are blank, the formula updates a blank (through "" string). This displays well in SharePoint. But when I refresh the linked table in MS Access, I get a #Error value for the FIN_PCEDate for the first record.

    Interestingly, this #Error comes up only for the calculated field.

    How can I overcome this?
    Last edited by skbpillai; 01-29-2016 at 04:23 AM. Reason: Image copy-paste did not work. Hence re-loaded screenshot

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try iif rather than if

    also, dates are numbers not text, so Changed_PCEDate="" won't work, the field will be null so try

    iif(isnull(Changed_PCEDate),....

  5. #5
    skbpillai is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2016
    Posts
    6
    Hi. I tried the suggestions you had mentioned. Here are the findings:
    1) IIF is not supported.
    2) Tried using isunll(Changed_PCEDate) instead of Changed_PCEDate="". It gives the same result.
    3) So, I put in a formula like this
    =IF(isnull(Changed_PCEDate),IF(isnull(PCEDate),,PC EDate),Changed_PCEDate)
    The difference is, instead of updating the calculated field as "" I tried NOT to update, by leaving the THEN Clause empty. Ironically, it puts in 12/30/1899!!! Which means, I am not able UPDATE this field with NULL DATE Value.

    Can you please suggest what can be done? Thanks.

  6. #6
    skbpillai is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2016
    Posts
    6
    I haven't found a resolution to this yet. All that I understand is, a NULL value for date is impossible to be set. If I use the expression "" in the IF THEN ELSE formula, it is a text-null and MS Access does not like it. If I let the field blank by NOT updating any value, SharePoint sets the date as 12/30/1899. Is there no way to set a NULL for a calculated-date field?

  7. #7
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if iif is not supported then not sure what you are using. I found this link which may help

    https://blogs.office.com/2012/03/19/...s-and-reports/

    but it uses iif, not if but does suggest you need to use formatdatetime to convert your date to a string

  8. #8
    skbpillai is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2016
    Posts
    6
    Hi Ajax, the link you shared points to MS Access side expressions. I was looking at more from SharePoint Calculated Column Formula to fix this. Further, this being a linked SharePoint list in MS Access, I am trying to avoid #Error being captured in the base table itself. So, I am afraid that it wouldn't solve my problem.

  9. #9
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    well I googled 'sharepoint calculated column' and found plenty of hits - looks like the structure is along the lines of excel, but I guess you know that. I'm not a sharepoint fan (found it too limiting and too many bugs, but that was a few years ago and I'm sure it will have improved since then) Since no one else is stepping in here I would find a sharepoint forum and post your question there. Sorry I can't help

  10. #10
    skbpillai is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jan 2016
    Posts
    6
    No worries, Ajax and Ranman. It was nice interacting with you. I appreciate the earnest attempts to help me out. I will, for now, leave this thread open. If any one of you finds a solution to this, please let me know. For now, I will hunt for SharePoint forums to post this topic. Yes, you can also post this on my behalf - feel free to.

  11. #11
    RudiB is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    1
    Quote Originally Posted by skbpillai View Post
    I have a list in SharePoint that I link in my MS Access Database. This list has a Calculated Field. It compares two date fields and updates a date value OR a blank. Now, when the records are sync'ed back in MS Access, all fields where it is left blank comes as #Error. Any pointers to mitigate this issue would be a great help.
    Try this: https://community.spiceworks.com/top...te-calculation

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linked Table shows deleted for all fields
    By nancyma in forum Programming
    Replies: 3
    Last Post: 10-01-2023, 11:29 AM
  2. Replies: 10
    Last Post: 01-23-2016, 12:29 PM
  3. Replies: 1
    Last Post: 09-03-2014, 03:23 PM
  4. Date Field - Access - from Linked Table
    By lynnmc26 in forum Access
    Replies: 4
    Last Post: 06-27-2014, 08:51 AM
  5. Replies: 4
    Last Post: 09-02-2010, 02:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums