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.
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.
- If the
NRbox is checked for(P)FSandIEThen FALSE - If the
NRbox is not checked for(P)FSAndIEand 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
- If
(P)FSorIEis required and If the current date is between 1/1/CYYY and 4/30/CYYYY and the Date in(P)FSorIEis between 10/1/PY2Y and 4/30/PYYY Then FALSE Else TRUE - If
(P)FSorIEis required and If the current date is between 5/1/CYYY and 9/30/CYYYY and the Date in(P)FSorIEis less than 1 year old Then FALSE Else TRUE - Last thing to keep in mind is that as long as there is an up-to-date
(P)FSorIEdocument, the result should be FALSE. They are both not required. It's either or.
This screenshot should help clarify the date formulas:
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