Solved

If Is Null and report

Posted on 2011-03-09
7
392 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

730 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