Solved

If Is Null and report

Posted on 2011-03-09
7
378 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
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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