+ Start a Discussion
Riley BRiley B 

Date in Query gives - Error Generated by request:: ExceptionCode : 5077 (a fix sort of)

Error Generated by request

from (object) where (field) [opporation] YYYY-MM-DDTHH:MM:SS.000Z

 

Error at row:1:Column:41

value of filter criterion for field 'xxxxx__C' must be of type date and should not be enclosed in quotes

ExceptionCode : 5077

 

 

i changed the code in the connector's utils Module

 

        'commented out to fix queries for field equals date
        'sfQueryValueFormat = Format$(vlu, "yyyy-mm-ddTHH:MM:SS.000Z")
       
        'changed format to fix queries for field equals date

        sfQueryValueFormat = Format$(vlu, "yyyy-mm-dd")

 

 

this seems to work so far in queries that use just a date.

 

no idea yet how it would impact queries that involved both date and time.

 

 

Has anyone else come up with a better solution ?

 

 

Use at YOUR OWN RISK. be safe make backups

 

 

Best Answer chosen by Admin (Salesforce Developers) 
bouscalbouscal

I've had success with the following with both date and datetime values;

 

Function sfQueryValueFormat(typ, vlu) Dim today As Date: today = Date Dim daychange As Variant, incr%: incr = 0 Select Case typ ' -- Date section added, "date" removed from "datetime" selection Case "date": If (InStr(LCase(vlu), "today")) Then If (InStr(LCase(vlu), "-")) Then daychange = Split(vlu, "-") incr = 0 - Int(daychange(1)) End If ' 12/09/09 addition If (InStr(LCase(vlu), "+")) Then daychange = Split(vlu, "+") incr = Int(daychange(1)) End If vlu = DateAdd("d", incr, today) End If ' 5.12 sfQueryValueFormat = Format$(vlu, "yyyy-mm-dd") ' -- End of change Case "datetime": ' was "datetime","date": ' ' 5.12 allow strings like ' today, today - 1 , today - 150, today + 30 ' to be translated into vba dates for the query... ' If (InStr(LCase(vlu), "today")) Then ' Dim today As Date: today = Date ' Dim daychange As Variant, incr%: incr = 0 If (InStr(LCase(vlu), "-")) Then daychange = Split(vlu, "-") incr = 0 - Int(daychange(1)) End If If (InStr(LCase(vlu), "+")) Then daychange = Split(vlu, "+") incr = Int(daychange(1)) End If vlu = DateAdd("d", incr, today) End If ' 5.12 end sfQueryValueFormat = Format$(vlu, "yyyy-mm-ddTHH:MM:SS.000Z") Case "double", "currency", "percent": ' add percent per Scot S. 5.67 If (InStr(vlu, ".")) Then sfQueryValueFormat = Val(vlu) ' if the double has a decimal already, dont need to add .0 Else sfQueryValueFormat = Val(vlu) & ".0" End If Case "boolean": sfQueryValueFormat = IIf((Val(vlu) Or "true" = LCase(vlu)), "TRUE", "FALSE") Case "int": ' 6.11 by scot stony sfQueryValueFormat = vlu Case Else: ' all which look like string, including but not limited to sfQueryValueFormat = "'" & vlu & "'" ' string, picklist, id, reference, textarea, combobox email End Select End Function

 

All Answers

Riley BRiley B

Epic Fail though when i tried to redistribute the XLA file within the company? 

 

Suggestions?

bouscalbouscal

I've had success with the following with both date and datetime values;

 

Function sfQueryValueFormat(typ, vlu) Dim today As Date: today = Date Dim daychange As Variant, incr%: incr = 0 Select Case typ ' -- Date section added, "date" removed from "datetime" selection Case "date": If (InStr(LCase(vlu), "today")) Then If (InStr(LCase(vlu), "-")) Then daychange = Split(vlu, "-") incr = 0 - Int(daychange(1)) End If ' 12/09/09 addition If (InStr(LCase(vlu), "+")) Then daychange = Split(vlu, "+") incr = Int(daychange(1)) End If vlu = DateAdd("d", incr, today) End If ' 5.12 sfQueryValueFormat = Format$(vlu, "yyyy-mm-dd") ' -- End of change Case "datetime": ' was "datetime","date": ' ' 5.12 allow strings like ' today, today - 1 , today - 150, today + 30 ' to be translated into vba dates for the query... ' If (InStr(LCase(vlu), "today")) Then ' Dim today As Date: today = Date ' Dim daychange As Variant, incr%: incr = 0 If (InStr(LCase(vlu), "-")) Then daychange = Split(vlu, "-") incr = 0 - Int(daychange(1)) End If If (InStr(LCase(vlu), "+")) Then daychange = Split(vlu, "+") incr = Int(daychange(1)) End If vlu = DateAdd("d", incr, today) End If ' 5.12 end sfQueryValueFormat = Format$(vlu, "yyyy-mm-ddTHH:MM:SS.000Z") Case "double", "currency", "percent": ' add percent per Scot S. 5.67 If (InStr(vlu, ".")) Then sfQueryValueFormat = Val(vlu) ' if the double has a decimal already, dont need to add .0 Else sfQueryValueFormat = Val(vlu) & ".0" End If Case "boolean": sfQueryValueFormat = IIf((Val(vlu) Or "true" = LCase(vlu)), "TRUE", "FALSE") Case "int": ' 6.11 by scot stony sfQueryValueFormat = vlu Case Else: ' all which look like string, including but not limited to sfQueryValueFormat = "'" & vlu & "'" ' string, picklist, id, reference, textarea, combobox email End Select End Function

 

This was selected as the best answer
GuyClairboisGuyClairbois

this worked for me. Tnx.