Continuous Form #Error On New Record

[SunnyTaru]

Hi
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"))

Appreciate guidance.

Thanks
Taruna

[NeoPa]

Hi Taruna.

Your post seems to indicate you're working with a Query rather than a Table. I'll work on that assumption, but you probably know the fewer assumptions we are left with the better. A clear explanation is always very important for any question.

Actually, I tried various approaches to reproduce something like your problem but it never ended up with an updatable query even. Perhaps you should explain exactly what your situation is and we can go from there.

[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]

Hi Sunny.

I hope you get your account situation resolved soon. Technically, we don't allow members to create multiple accounts, but I appreciate there's little choice for you at the moment.

I would guess your problem is related to your complicated IIf() call. As far as I understand, it should work even for the New record. However, I don't know how it will react to the use of the IsNull() function instead of the SQL native Is Null comparison.

Try :

IIf([EOIDueDate] Is Null,'N/A',
IIf([EOIReceivedDate] Is Null,
IIf([EOIDueDate]<Date(),'Over Due','Not Due'),
IIf([EOIReceivedDate]>[EOIDueDate],'Late','On Time'))) AS [PIFRespStat]

It simplifies the code as well as making it make better sense. Switch() is now redundent as it adds nothing you want or need.

NB. I laid the code out for better visibility on this page but there's no need for that when using it. Just strip the new lines from it.

[TMatharu]

Hello NeoPa,

Thanks for your time. The code is gives invalid syntax error.

Thanks

TMatharu

[isladogs]

On a quick glance, line 4 of above code has a missing comma after [EOIDueDate]. Add that and see if it helps

[NeoPa]

Spot on isladogs. Ta.

@Sunny.

I've corrected the original now so if you copy from it again it should work :-)