+ Start a Discussion
Lahari KondadiLahari Kondadi 

How to get the records using dynamic soql for the date field which is equal to NULL along with the range of date values?

Hi experts,
I have a requirement where the user should be able to include the records where the due date is not populated when i check the checkbox field in the vf page.In my case that date field is named as Due_Date__c.
I have written the follwing dynamic soql code.But it is not fetching the records where the due date is not populated.Please help
public class PrivateEquityReport
   ApexPages.StandardSetController regionStandardSetController{get;set;}
   private Id privateRecordTypeId = Fund_Activity__c.SObjectType.getDescribe().getRecordTypeInfosByName().get(' Amendment').getRecordTypeId();
   public AI_PrivateEquityWeeklyTrackingReport()
   String baseQuery = 'SELECT Id,Due_Date__c,Fund__r.Name,Company__r.Name,Subject__c,Comments__c,Date_Received__c,Business_Review__c,Attorney_Review__c FROM Fund__c WHERE Team__c=\'Equity\' AND RecordTypeId =:privateRecordTypeId';

   public PrivateEquityReport()
    regionStandardSetController=new ApexPages.StandardSetController(new  list<Fund__c>());
    String dueDateCheck=System.currentPageReference().getParameters().get('DueDateCheck');

public String  getReportText()
    String checkQuery = baseQuery;

     String reportFromDateVar = System.currentPageReference().getParameters().get('dFromDate');
     Date fromDateVar = null;
    if(reportFromDateVar != null)
    fromDateVar = Date.valueOf(reportFromDateVar);
    System.debug('*****From Date***'+fromDateVar );

     String reportToDateVar = System.currentPageReference().getParameters().get('dToDate');
     Date toDateVar = null;
    if(reportToDateVar != null)
    toDateVar = Date.valueOf(reportToDateVar );
     System.debug('*****To Date***'+toDateVar );

     if((fromDateVar!= null)&&(toDateVar!=null)&&(!isDueDate))
      checkQuery =checkQuery +' '+'and (Due_Date__c>=:fromDateVar AND Due_Date__c<= :toDateVar)';
      checkQuery =checkQuery +' '+'and Due_Date__c=NULL';

    regionStandardSetController=new ApexPages.StandardSetController(Database.getQueryLocator(checkQuery));
    activityRecords= regionStandardSetController.getRecords();
     displayPrivateEquity ='<table><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>' + displayPrivateEquity ;                
     displayPrivateEquity = prepareReportSinglePagePrivateEquity(activityRecords,displayPrivateEquity );
     return  displayPrivateEquity; 
Best Answer chosen by Lahari Kondadi
Abhishek BansalAbhishek Bansal

Hi Lahari,

I think you should use OR operator instead of AND operator at line no.37.
Please update your code by using OR operator at line 37 and than try.

If you still face any problem than please let me know.

Abhishek Bansal