vendredi 4 mars 2016

Complex Access Formula Logic

I am having some trouble getting some complex IF logic to work inside an MS Access query and am feeling fried at this point, so I am hoping a fresh pair of eyes can help me solve my issue.

The following screenshot I hope will help make my logic more clear.

enter image description here

My end result with the formula is to determine if a borrower (Borr) is out-of-date with submitted documents. In this case, I only care about the (P)FS and / or IE documents.

The conditions for being out-of-date are a bit complex and I will explain them below. TRUE equates to being out-of-date and FALSE to being compliant.

  1. If the NR box is checked for (P)FS and IE Then FALSE
  2. If the NR box is not checked for (P)FS And IE and the (P)FSdate is blank ANDIE` date is blank Then TRUE

This is easy enough, but now for the tricky part CYYY = current year, PYYY = prior year, PY2Y = 2 years ago

  1. If (P)FS or IE is required and If the current date is between 1/1/CYYY and 4/30/CYYYY and the Date in (P)FS or IE is between 10/1/PY2Y and 4/30/PYYY Then FALSE Else TRUE
  2. If (P)FS or IE is required and If the current date is between 5/1/CYYY and 9/30/CYYYY and the Date in (P)FS or IE is less than 1 year old Then FALSE Else TRUE
  3. Last thing to keep in mind is that as long as there is an up-to-date (P)FS or IE document, the result should be FALSE. They are both not required. It's either or.

This screenshot should help clarify the date formulas:

enter image description here

This is the last Access formula I have entered but still doesn't work. I also think I know why, but I am too fried with this to try to continue to solve for the moment:

    B1-FS-IE: IIf([annual_review].[prop_cd]<>"0056" And [nr_fs_b1]=True And 
[nr_ie_b1]=True,False,IIf(([nr_fs_b1]=False And IsNull([fs_b1])) And ([nr_ie_b1]=False 
And IsNull([ie_b1])),True,IIf(Month([fs_b1])>9 Or Month([fs_b1])<5 
And (Month(Date())<5 And Date()-[fs_b1]>576) Or (Month([fs_b1])>4
 And Month([fs_b1])<10 And Date()-[fs_b1]>=365) Or Month([ie_b1])>9 
Or Month([ie_b1])<5 And (Month(Date())<5 And Date()-[ie_b1]>576) 
Or (Month([ie_b1])>4 And Month([ie_b1])<10 And Date()-[ie_b1]>=365),True,False)))

Finally, I placed the Excel tag because if someone can work this out in Excel, I can translate to Access.

Aucun commentaire:

Enregistrer un commentaire