+ Start a Discussion
Scott PolkScott Polk 

MALFORMED_QUERY using formula field

Hi all,
 
First, let me say that i am new to developing with SalesForce and the API.  I am using the Partner WSDL to connect to SF.  I am trying to pull a query that contains a formula field in the condition clause, but i am getting a MALFORMED_QUERY error saying 'unexpected token Next_Invoice_Date__c'.  I have searched and searched for an answer, but i cant seem to find one.
 
My query (VB.NET):
Code:
Dim SFqr As SForce.QueryResult = SFService.query("SELECT Id, Name, ACCT_Last_invoice_date__c, Next_Invoice_Date__c FROM Opportunity " _
                & "WHERE OPS_Lease_Begin_Date__c != null " _
                & "AND Next_Invoice_Date__c <= " & sMaxDate & " " _
                & "AND (Turn_off_billing_date__c = null OR Turn_off_billing_date__c > Next_Invoice_Date__c) " _
                & "AND (ACCT_Last_invoice_date__c != Next_Invoice_Date__c AND ACCT_Last_invoice_date__c != null)")

 The variable sMaxDate is populated with a date in the format yyyy-MM-dd.  The text in red is what the error is referring to.  What i dont understand is why it is excepting the first Next_Invoice_Date__c (in orange), but not the second.

Can someone please advise me on what to do?
 
Thanks in advance
 
~Scott~
Gareth DaviesGareth Davies
Scott

have you tried usign SForce Explorer to debug this query?

If drop me an email and I will send you a pre-release of  DataScope which is in final testing and may help.

Cheers
Gareth.
Scott PolkScott Polk
Call me crazy, but i cant seem to get the sForce Explorer to work.  It's not connecting because of a proxy authentication error.
EnderEnder
Scott,
 
The query is invalid soql.  Right now it is not valid to include a field in the right side of an expression.  It must be a literal value.
 
Just as "Select name from account where ownerid = lastModifiedId" is invalid  your comparison of "Turn_off_billing_date__c > Next_Invoice_Date__c" is invalid.
 
Next_Invoice_Date__c must be a value.
Scott PolkScott Polk
Thanks Ender

I guess i will have to whip out some nasty workaround then.  :smileyhappy:

~Scott~