Solved

If Is Null and report

Posted on 2011-03-09
7
354 Views
Last Modified: 2012-05-11
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
Comment
Question by:pdvsa
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:pdvsa
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:pdvsa
Comment Utility
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
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now