Continuous Form #Error On New Record (Closed)

[TMatharu]

Hi
I had posted this issue last week, I got a response but I was having issues with login so had to created another account, I had sent email to the "contacts us" for help awaiting response.

I do see a response from Neo Pa but I was unable to respond back. Below is a query and the I have for a subform displaying results.

SQL

SELECT PIFNewProjectTBL.ProjectID, PIFNewProjectTBL.MemberID, PIFNewProjectTBL.PDocumentType, PIFNewProjectTBL.PDocuTypeRev, PIFNewProjectTBL.EOIDate, PIFNewProjectTBL.EOIDueDate, PIFNewProjectTBL.EOIReceivedDate, IIf(IsNull([EOIDueDate]),"N/A",Switch([EOIReceivedDate]<=[EOIDueDate],"On Time",[EOIReceivedDate]>[EOIDueDate],"Late",[EOIReceivedDate] Is Null And [EOIDueDate]<Date(),"Over Due",[EOIDueDate]>Date(),"Not Due")) AS PIFRespStat
FROM PIFNewProjectTBL;

I am having issue with New record " #ERROR "
The logic works fine except that on the new record row it displays #Error. I know it's doing that because the EOIDate and EOIDueDate is blank as (New) and will be so until a new record is entered. Is there a way that I can get it to show blank (or 0) for the new record row? EOI due date is auto calculated once EOI Date is entered.

IIf(IsNull([EOIDate]),0,Switch([EOIReceivedDate]<=[EOIDueDate],"On Time",[EOIReceivedDate]>[EOIDueDate],"Late",[EOIReceivedDate] Is Null And [EOIDueDate]<Date(),"Over Due",[EOIDueDate]>Date(),"Not Due"))

[NeoPa]

I'm going to close this one as it makes more sense to merge it with your earlier thread. That thread can be found at Continuous Form #Error On New Record.