?
Solved

If Is Null and report

Posted on 2011-03-09
7
Medium Priority
?
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:pdvsa
ID: 35082581
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35082656
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
ID: 35082659
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:pdvsa
ID: 35082961
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 2000 total points
ID: 35082971
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35083034
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
ID: 35083421
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

752 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