[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

If Is Null and report

Experts, I have this grouping on my report:
MyGroup: IIf(IsNull([tblLCAmendHistory].[DateAmendApprovedByBank]),"Awaiting Amend Approval by Bank","Recently Approved Amend by Bank")

the Grouping divides records between Amendments that "have not been approved" and "have been approved" based on whether or not the [DateAmendApprovedByBank] Is Null or Is Not Null.  If it [DateAmendApprovedByBank] is Null then it is "Awaiting Approval by Bank".

What I want to do is limit the records in the Group: "Recently Approved by Bank" as the list is pretty long.  I would like a criteria to only show 1 month from current date based on [DateAmendApprovedByBank].  

I thought I could drag down the [DateAmendApprovedByBank] and use a between formula (current date - 1 month) but that would remove any records that have not been approved and or course I need to show those records that have not been approved for >1month.

Any questions please let me know.  (SQL will be posted below)
0
pdvsa
Asked:
pdvsa
  • 3
  • 2
  • 2
1 Solution
 
pdvsaAuthor Commented:
SELECT tblLCAmendHistory.*, tblEndUser.[End User],
IIf(IsNull([tblLCAmendHistory].[DateAmendApprovedByBank]),"Awaiting Amend Approval by Bank","Recently Approved Amend by Bank") AS MyGroup, tblLetterOfCredit.ExpiredYN

FROM (tblLCAmendHistory INNER JOIN tblEndUser
ON tblLCAmendHistory.EndUserID = tblEndUser.EndUserID)
LEFT JOIN tblLetterOfCredit ON tblLCAmendHistory.letterofcreditID = tblLetterOfCredit.LetterOfCreditID;
0
 
Rey Obrero (Capricorn1)Commented:
you can use the criteria you have mentioned above, but need to add another criteria on your second condition..
<but that would remove any records that have not been approved and or course I need to show those records that have not been approved for >1month. >

how do you determine records that have not been approved and the age is > 1 month?
0
 
derekkrommCommented:
Give this a try:
SELECT tblLCAmendHistory.*, tblEndUser.[End User], 
IIf(IsNull([tblLCAmendHistory].[DateAmendApprovedByBank]),"Awaiting Amend Approval by Bank","Recently Approved Amend by Bank") AS MyGroup, tblLetterOfCredit.ExpiredYN

FROM (tblLCAmendHistory INNER JOIN tblEndUser 
ON tblLCAmendHistory.EndUserID = tblEndUser.EndUserID) 
LEFT JOIN tblLetterOfCredit ON tblLCAmendHistory.letterofcreditID = tblLetterOfCredit.LetterOfCreditID;
WHERE [tblLCAmendHistory].[DateAmendApprovedByBank] IS NULL OR [tblLCAmendHistory].[DateAmendApprovedByBank] >= dateadd(d, -30, Date())

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
pdvsaAuthor Commented:
derek:  I get an error of "Characters found after SQL statement"

Cap:  <how do you determine records that have not been approved and the age is > 1 month?
I woudl want to show these records.  There should be no criteria for records that have not been approved.  They should always be shown.  For ones that have been approved (Is Not Null[DateAmendApprovedByBank] , limit to 1 month.  Hope that answers your question.

<you can use the criteria you have mentioned above, but need to add another criteria on your second condition..
I am not sure how to write this second condition properly.  I dont knwo if it is Or or And (I get confused on those)...I must have it right and this is why I resort to the Experts .
0
 
derekkrommCommented:
sorry, i left a ";" in there
SELECT tblLCAmendHistory.*, tblEndUser.[End User], 
IIf(IsNull([tblLCAmendHistory].[DateAmendApprovedByBank]),"Awaiting Amend Approval by Bank","Recently Approved Amend by Bank") AS MyGroup, tblLetterOfCredit.ExpiredYN

FROM (tblLCAmendHistory INNER JOIN tblEndUser 
ON tblLCAmendHistory.EndUserID = tblEndUser.EndUserID) 
LEFT JOIN tblLetterOfCredit ON tblLCAmendHistory.letterofcreditID = tblLetterOfCredit.LetterOfCreditID
WHERE [tblLCAmendHistory].[DateAmendApprovedByBank] IS NULL OR [tblLCAmendHistory].[DateAmendApprovedByBank] >= dateadd(d, -30, Date());

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you can not have a condition like
<<course I need to show those records that have not been approved for >1month. >

without having a basis to count from, to get a 1 month old record that was not approved.
0
 
pdvsaAuthor Commented:
Derek:  that seeemed to work.  I tested it with this criteria on [DateAmendApprovedByBank]:
Is Null Or >=DateAdd("d",-30,Date())

That seemed to be the solution.  I tested as many ways as I could and I could not make it fail. It includes in the Group any record that has not been approved in the First Group "Awaiting Amend Approval by Bank" and it includes in the second group "Recently Approved Amend by Bank" only records that are within 1 month from current date that is Not Null on [DateAmendApprovedByBank]

Cap:  <without having a basis to count from, to get a 1 month old record that was not approved
It seems to include records that are > or < 1 month that were not approved by having this stmnt:Is Null Or >=DateAdd("d",-30,Date())

(I have written a lot of words but it is mainly to help myself understand it which i still dont completely understand it but it seems to work.)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now