Access #Errors

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:

Answer 1

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

Answer 2

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.

Answer 3

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? 

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

i dont see the images.

Answer 8

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

Answer 9

ok i got it to work thanks for everyones help