You need to sign in to do that
Don't have an account?
Datagirl
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:
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.
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!