"Date" field in search criteria isn't formatted correctly.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Run, Describe wizard for Opportunity object or input following values.
  A1 -> Opportunity
  A2 -> Id

2. Input search criterion as follows directly or on Query wizard.
  - Field
    CloseDate
  - Operator
    Anything
  - Value
    Any day

  B1 -> CloseDate
  C1 -> > (greater than)
  D1 -> Any date(eg. 2011/1/1)

3. Invoke the query on "Query Table Data".

What is the expected output? What do you see instead?
  - Expected output
    Returned query result correctly

  - Actual result
    Error Generated by request::
    select id from Opportunity where CloseDate > 2010-10-01T00:00:00.000Z

    ERROR at Row:1Column:35
    value of filter criterion for field 'CloseDate' must be of type date and should not be enclosed in quotes
    ExceptionCode: 5077

What version of the product are you using? On what operating system?  With what 
Salesforce.com edition?
  - sforce Connector for Excel
    Version 6.16 Feb 17, 2006

  - sforce Toolkit for Office
    V3.0

  - OS
    WinXP(SP3)

  - Salesforce.com edition
    EE and DE

Please provide any additional information below.
  -- Improvised workaround (For your information) --

  1. Open standard module named "utils".
  2. Modify function sfQueryValueFormat as follows.
    The case closure in this function detects and adjusts each type of value, though. 
    You know, Date type value should be sent as like "YYYY-MM-DD", but the adjustment format the value to "yyyy-mm-ddTHH:MM:SS.000Z".

    So I added If closure.

    If (typ = "datetime") Then
       sfQueryValueFormat = Format$(vlu, "yyyy-mm-ddTHH:MM:SS.000Z")
    Else
       sfQueryValueFormat = Format$(vlu, "yyyy-mm-dd")
    End If

  I hope this workaround helps you.
  Thank you.

Original issue reported on code.google.com by m.t.t.k.1AE@gmail.com on 30 Mar 2011 at 1:57

[GoogleCodeExporter]

The workaround provided fixed this error for me. THANK YOU!!

Original comment by jojogent...@gmail.com on 8 Apr 2011 at 10:44

[GoogleCodeExporter]

thanks for sending the fix, will include it in a future build

Original comment by vneh...@gmail.com on 6 May 2011 at 3:13

• Changed state: Accepted
• Added labels: Usability

[GoogleCodeExporter]

I don't understand " 1. Open standard module named "utils"."   Where am i 
supposed to find this?

Original comment by mark.mil...@gmail.com on 23 May 2011 at 11:11

[GoogleCodeExporter]

I meant that you should open VBA editor to edit the module.
While you're running Excel and opening XLS file in your computer, you should 
press [Alt] + [F11] then the VBA editor will appear.
Please take a look at attached picture. 

Best regards.

Original comment by m.t.t.k.1AE@gmail.com on 24 May 2011 at 5:12

Attachments:

• notitle.JPG

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I'm using PE, sforce_connect_pe_winter11.xla, on Windows Vista, using Office 
10. And Sforce Toolkit version 4.0.0.10

In VBA editor, I attempt to open the sforce_connector 
(sforce_connect_pe_winter11.xla) to fix this problem as described above -- that 
is click on the '+' to expand the module, a sforce_connector Password box pops 
up expecting a password.  Apparently this module is now password protected.  
Can anyone provide the password so I can fix this and run these queries?

Original comment by jd...@northplains.com on 20 Jun 2011 at 5:23

[GoogleCodeExporter]

You don't need a password to edit the VBA code, only to access your Salesforce 
data.

Original comment by jojogent...@gmail.com on 29 Jun 2011 at 5:23

[GoogleCodeExporter]

So it is my Salesforce password that I need to enter at this point?

Original comment by jd...@northplains.com on 5 Jul 2011 at 9:35

[GoogleCodeExporter]

Thanks: Works a treat.

Original comment by mikeolg@gmail.com on 8 Nov 2011 at 3:52

[GoogleCodeExporter]

Sweet, this works. Whatever fix was applied in 2011 Excel Connector, didn't 
work. But this code above, did. 

Original comment by gpedr...@c-levelcompanies.com on 12 Dec 2011 at 10:18

[GoogleCodeExporter]

Need to implement the date fix, but I get the same password prompt as 
jd....@northplains.com    HELP!!!!

Original comment by david%en...@gtempaccount.com on 20 Jan 2012 at 5:23

[GoogleCodeExporter]

Sounds like you are running the VBA script and not editing the code.

The most current version of Excel Connector for Enterprise and Unlimited has 
resolved this issue; no VBA edit required.

Original comment by jojogent...@gmail.com on 20 Jan 2012 at 5:59

[GoogleCodeExporter]

Can I run it if I use Professional Edition?

Original comment by david%en...@gtempaccount.com on 20 Jan 2012 at 6:03

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I get the same password issue in VB--it asked for an sforce_connector Password 
when I attempt to open script.

Using Excel 2010 and SF Professional Edition.

Original comment by glenfoss...@gmail.com on 26 Apr 2012 at 3:27

[GoogleCodeExporter]

I've bumped into this query issue on an XP SP3 workstation as well. Is this OS 
specific? I also cannot try this workaround as the VBA editor prompts for a 
password. 

We have the exact same query running just fine via another instance of the 
connector on a Mac running Excel within a virtual environment.

Original comment by andrew.m...@longbottomcoffee.com on 19 Jun 2012 at 7:11

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

From what I understand you can not edit the VBA code for the PE; it contains 
code that allows access to the API (which normally isn't allowed in PE) and 
thusly was required to be password protected; VBA password are weak.

Original comment by jensbo...@gmail.com on 10 Jul 2012 at 7:00

[GoogleCodeExporter]

I just installed the connector (September 24th, 2012) and this is still an 
issue. Thanks for the work around.

Original comment by georgie....@gmail.com on 24 Sep 2012 at 6:42

[GoogleCodeExporter]

This is still an Issue I installed the following.
M.S. Excel 2010
Connector:
sforce_connector_ee_1603.zip    Excel Connecor for Salesforce.com Enterprise and 
Unlimited Editions ( API 16.0 )   Featured  Jul 2011    Jul 2011 

Office Tookit: 
SForce_Office_Toolkit_MSI.zip   Office Toolkit 4.0 MSI ZIP   Featured   Nov 2010

I get the same error. It does not appear this patch has been applied. How can I 
help get this into the next release?

Original comment by stke...@gmail.com on 28 Nov 2012 at 12:09

[GoogleCodeExporter]

This was really frustrating me and if it wasn't for you it still would be. A 
Big Thank You to you! 

Original comment by drolling...@gmail.com on 23 Apr 2014 at 3:21