none
Access #Errors RRS feed

  • Question

  • Hello,

    I am using access 2019 and I have the tables connected to a SharePoint List (code below). So my issue is I have a calculated column that is calculating a date. In SharePoint the column isn't showing an error or null it working correctly, but in access its showing a #Error. I refreshed the table and its still showing the error... is there something that can be done via a query or something so i can put that data on a report without that erroring out.

    =IF(OR(ISERR([BFR Date]),ISBLANK([BFR Date])),"",DATE(YEAR([BFR Date])+2,MONTH([BFR Date])+1,1))

    Error on access report:

    Thursday, August 15, 2019 9:27 PM

Answers

  • ciao mrDull,

    I would really avoid calculated fields into table for this.

    Anyway, not sure to have understood 100% your issue, but try this suggestion on calculated field in a query :

    IIf(Not IsNull([BFR Date]),DateSerial(Year([BFR Date])+2,Month([BFR Date])+1,1)) AS Mydate

    SELECT …...,IIf(Not IsNull([BFR Date]),DateSerial(Year([BFR Date])+2,Month([BFR Date])+1,1)) AS Mydate
    FROM yourTable;
    ciao, Sandro.

    • Marked as answer by mrdull Wednesday, August 21, 2019 9:18 PM
    Friday, August 16, 2019 7:19 AM

All replies

  • Hi

    I dont understand the +1,1 on a date??

    Try antother syntax, in my case in a query:

    BFR Expire C: IIf(IsNull([BFR Date]);"";CDate([BFR Date]+(730)*1,1))

    Or this:

     IIf(IsNull([BFR Date]);"";CDate((803+[BFR Date])))


    Cheers // Peter Forss Stockholm


    Friday, August 16, 2019 5:04 AM
  • ciao mrDull,

    I would really avoid calculated fields into table for this.

    Anyway, not sure to have understood 100% your issue, but try this suggestion on calculated field in a query :

    IIf(Not IsNull([BFR Date]),DateSerial(Year([BFR Date])+2,Month([BFR Date])+1,1)) AS Mydate

    SELECT …...,IIf(Not IsNull([BFR Date]),DateSerial(Year([BFR Date])+2,Month([BFR Date])+1,1)) AS Mydate
    FROM yourTable;
    ciao, Sandro.

    • Marked as answer by mrdull Wednesday, August 21, 2019 9:18 PM
    Friday, August 16, 2019 7:19 AM
  • Thanks FYI the formula that is shown above is in the sharepoint list and not in the access database. When you look at the access database it shows the #error, but when you look at the SharePoint list it shows it as a blank. Will the above formulas go in the query take out the #error? 
    Friday, August 16, 2019 8:27 PM
  • Hi

    I would say Yes.

    The syntax for "blanks" in Access is IIF (Imediatly IF) IIF(IsNull([FIELD]);""; [FIELD]) 

    The "" leavs a "blank"


    Cheers // Peter Forss Stockholm

    Friday, August 16, 2019 8:59 PM
  • So that doesn't work, it doesnt even let me see the datasheet. On the access query sheet is says #Error... I just want those made into blanks...

    just to be clear you wanted me to type in IIF(IsNull([FIELD]),"", [FIELD]) in the criteria field in the query.

    thanks


    Tuesday, August 20, 2019 12:11 AM
  • Hi mrdull

    No it is not in the critera.

    Look at the 3 following pics:

    1. The Table

    2. The Query. (

    SELECT Table1.Sector, IIf(IsNull([bfr]),"",[bfr]+830) AS [BFR Expire C]
    FROM Table1;

    )

    3 The Result when running the Query


    Cheers // Peter Forss Stockholm


    Tuesday, August 20, 2019 5:38 AM
  • i dont see the images.
    Tuesday, August 20, 2019 11:57 PM
  • Hi mrdull

    No images? Well that is strange. I can see them in four different browsers - Chrome, Edge, Opera and Explorer.

    What browser do you have?

    Can you see your own image, the one you posted August, Thursday 15? 


    Cheers // Peter Forss Stockholm


    Wednesday, August 21, 2019 4:07 AM
  • ok i got it to work thanks for everyones help
    Wednesday, August 21, 2019 9:18 PM