470,593 Members | 2,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Continuous Form #Error On New Record

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.
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 24 '22 #1
6 8777
NeoPa
32,298 Expert Mod 16PB
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.
Mar 24 '22 #2
TMatharu
4 Nibble
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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.
Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([EOIDate]),0,Switch([EOIReceivedDate]<=[EOIDueDate],"On Time",[EOIReceivedDate]>[EOIDueDate],"Late",[EOIReceivedDate] Is Null And [EOIDueDate]<Date(),"Over Due",[EOIDueDate]>Date(),"Not Due"))
Apr 1 '22 #3
NeoPa
32,298 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. IIf([EOIDueDate] Is Null,'N/A',
  2. IIf([EOIReceivedDate] Is Null,
  3. IIf([EOIDueDate]<Date(),'Over Due','Not Due'),
  4. 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.
Apr 2 '22 #4
TMatharu
4 Nibble
Hello NeoPa,

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

Thanks

TMatharu
Apr 2 '22 #5
isladogs
352 Expert Mod 256MB
On a quick glance, line 4 of above code has a missing comma after [EOIDueDate]. Add that and see if it helps
Apr 2 '22 #6
NeoPa
32,298 Expert Mod 16PB
Spot on isladogs. Ta.

@Sunny.

I've corrected the original now so if you copy from it again it should work :-)
Apr 3 '22 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Leo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.