Apex Code Development You need to sign in to do that
Don't have an account?
Jimmy Jay Greater than comparison for Date in SOQL statement
I currently have an SOQL statement in my Apex extenstion for a visualforce page with the following
CGM = [Select id FROM OpportunityLineItem WHERE Opportunity.Owner.ID = :cntact.id AND Opportunity.CloseDate >= :beginDate ];
When I run the Visual Force page I receive the following error:
Argument 1 cannot be null
An unexpected error has occurred. Your development organization has been notified.
However when I change the SOQL statement to the following(without the > in the bold text) there is no error on the visual force page. It even pulls the Opportunity products from Opporunities that closed on beginDate.
CGM = [Select id FROM OpportunityLineItem WHERE Opportunity.Owner.ID = :cntact.id AND Opportunity.CloseDate = :beginDate ];
This is the code for beginDate. byear and bmonth are integers set by the visualforce page.
public Date beginDate;
public Date getBeginDate(){
if(bmonth!=null && byear!=null){
beginDate = date.newInstance(Integer.valueOf(byear),Integer.valueOf(bmonth),1);
return beginDate;
}else{
return null;
}
}
Like I said everything is working as expected without the > but breaks when it is added.
Thanks in advanced for your help.
-JimmyJ
Try building a string and using Database.query() instead of the bracket notation for the SOQL query. Looks like the person in this post got it to work:
http://boards.developerforce.com/t5/General-Development/Date-comparison-in-Salesforce/m-p/336361#M60661
Regards, jh
All Answers
Create a field and hide it from all pagelayouts which calculates your Date condition and then use it in the SOQL query
The date is set by the month and year the user selects from dropdowns on the visualforce page, it is not based on data on the Opportunity Product or Opportunity.
Try building a string and using Database.query() instead of the bracket notation for the SOQL query. Looks like the person in this post got it to work:
http://boards.developerforce.com/t5/General-Development/Date-comparison-in-Salesforce/m-p/336361#M60661
Regards, jh
Thank you, Database.query() worked.
Final query incase someone needs to see the escape characters. Wasn't sure why :cntact.id was invalid and :beginDate was valid but the below worked:
CGM = Database.query('Select id, Opportunity.Deal_Term__c, Opportunity.Type, Opportunity.Name, Product_Revenue_Type__c, , PricebookEntry.Name
FROM OpportunityLineItem
WHERE Opportunity.Owner.ID = \''+cntact.id+'\' AND Opportunity.CloseDate >= :beginDate AND Opportunity.CloseDate < :endDate AND Opportunity.IsWon=True AND (Product_Revenue_Type__c=\'License\' OR Product_Revenue_Type__c=\'Services\') AND (Opportunity.Order_Type__c=\'Type 0\' OR Opportunity.Order_Type__c=\'Type 1\' OR Opportunity.Order_Type__c=\'Type 2\' OR Opportunity.Order_Type__c=\'Type 3\' ) ');
Haha nice work! Glad that worked :)
Also wondering if this is a bug? Syntactically the bracket notation should accept the same string as a valid one that would be passed to Database.query() right?