You need to sign in to do that
Don't have an account?
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
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
Epic Fail though when i tried to redistribute the XLA file within the company?
Suggestions?
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 worked for me. Tnx.