Apologies for the delay in responding, I have been away from this project for the past week, but still been mulling things over in my mind.
My anti-virus appears to be set up in the exact same way as my colleague's so I don't think that ever had anything to do with it (not saying that in a sassy way, but purely my thoughts based on what I'm about to say next).
Seems as though I have solved the issue as I decided to test the export and the dataset to try and find which records had been causing the issue as the export button appeared to be "intermittently" working.
I exported my "Export" a few dates at a time and compared them against the records in my table. With there being 9,000+ rows I did 500 at a time until I hit the error message when exporting rows 8,000 - 8,500. I then traced it to row 8,386 where one of the ATA Chapters was set to REQUIRED. I then realised straight away that someone had accidentally left the field marked as the default value of "REQUIRED" and not the ATA Chapter. The user is meant to identify it says REQUIRED and then click the drop down to select the ATA Chapter such as "##-## SOME TEXT HERE" but with it saying "REQUIRED", it broke the calculated columns.
The error message Access was giving me was quite confusing, but everything appears to be working now that I have changed the value and I have changed the data validation logic for the input form.