function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
DatagirlDatagirl 

sforce connector date format error message-Please help

I just started using the sforce connector to build queris for a customized opportunity report in excel. All queries were working fine until excel crashed on me. When I go back into excel to re-run the queries, I'm get an error that states:
 
Error Generated by request::
from OpportunityLineItem where Service Date >2008-08-01TO0:00:00.00Z
 
ERROR at Row:1:Column:43
value of filter criterion for field 'service date' must be of type date and should not be enclosed with quotes????
Exception code: 5077
 
Then I would get the error: No data returned for this query.
 
I've changed the date format and I still get this error. I dont know what is causing this. Has anyone had this same issue? Can anyone help me please? :smileysad:
lmjohnsonlmjohnson
This may or may not answer your question, however, when ever I get these "funky" errors when using the connector, I have found out that I am "logged out" - not sure how it happened.... Anyhow, I will log back in and everything seems to work fine.
DatagirlDatagirl
Funny you say that, because it would force me to login everytime it goes through a table within the query. I've tried creating a new query with a new excel worksheet and I still got the same message. Thanks for the suggestion.
WendyJillWendyJill
hi--did you ever find a solution for this?  I'm going through the same thing right now--I can't use date as a criterion at all...thanks!
arnt72arnt72
Hi,
I get this error only if I use an endpoint higher than the 6.0 (Options - Advanced - Specify Default Server URL: you can then select the endpoint and thus API version when you log in). I thing the newer API versions require dates in a different format (date only) while the 6.0 accepted datetime for date fields. The connector converts all dates into datetime format. Try to make sure you use the 6.0 endpoint (https://www.salesforce.com/services/Soap/c/6.0) and it should work.
However, usually using a newer API than 6.0 is desired. Toggling between different endpoint URLs requires logout and login so I tried to find the code in the .xla that does the formatting and modified it

If you are familiar with editing vba, you can find it in sforce_connector (sforce_connect.xla) in the module utils in the function sfQueryValueFormat.

Works fine for me so far, however, I am not an expert in vba, please try at your own risk.


WendyJillWendyJill
thank you--it worked!  crazy that I have to switch between v6.0 and v13.0 to get different pieces to work, but it's better than updating in SF itself. thanks for the advice.
thunksalotthunksalot
Thanks arnt72!  I made the change in the utils module and everything works great without my having to change API versions!

 

I'm not a VBA expert either, but I think the change I made keeps both the date and datetime functionality working (I don't ever use datetimes, so can't say for sure):

 

I replaced:

 

        

sfQueryValueFormat = Format$(vlu, "yyyy-mm-ddTHH:MM:SS.000Z")
 
 
With this:
 
        If typ = "datetime" Then
            sfQueryValueFormat = Format$(vlu, "yyyy-mm-ddTHH:MM:SS.000Z")
        Else
            sfQueryValueFormat = Format$(vlu, "yyyy-mm-dd")
        End If
 
 
If you are like me and you don't use datetimes for anything, you could just replace the line above with this and not worry about the if/else statement:
 
 
         sfQueryValueFormat = Format$(vlu, "yyyy-mm-dd")
 
If someone who isn't a VBA expert or hack wants to try this, it isn't very hard.  Just look in the Excel documentation for how to create or edit a macro.  Look for the option to open VBA or edit the macro in VBA.  That opens a new window where you can browse the components of the sforce_connector on the left.  Open the Modules folder and click on the "utils" file.  Search for the sfQueryValueFormat function and make the change mentioned above.  No more switching API versions!
Message Edited by thunksalot on 01-19-2010 07:36 PM
Message Edited by thunksalot on  01-19-2010 07:37 PM


ScrappyjunkScrappyjunk
thunksalot - your fix worked amazing (even 10 years later!!!). Thank you!!!!!!!!!!