ERROR 000358: Invalid expression Failed to execute (Select Layer By Attribute)

Question

I'm using ModelBuilder to extract a layer by field values.

I'm working on this field :

Field Properties

I tried this method from Extracting by Attribute Using ModelBuilder with User Input? and it worked for many fields but when i try it on a DATE field i got this error.

ERROR 000358: Invalid expression
Failed to execute (Select Layer By Attribute)

Here is my ModelBuilder :

ModelBuilder

Here is the script used in Calculate Value

import arcpy
def buildWhereClauseMultiValue(table, field, values):
 """Takes a semicolon-delimited list of values and constructs a SQL WHERE
 clause to select those values within a given field and table."""
 # Add DBMS-specific field delimiters
 fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(table).path, field)
 # Split multivalue at semicolons and strip quotes
 valueList = [value[1:-1] if (value.startswith("'") and value.endswith("'")) else value for value in values.split(';')]
 # Determine field type
 fieldType = arcpy.ListFields(table, field)[0].type
 # Add single-quotes for string field values
 if str(fieldType) == 'String':
 valueList = ["'%s'" % value for value in valueList]
 # Format WHERE clause in the form of an IN statement
 whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(valueList))
 return whereClause `

And finaly here's what i wrote in the model as date

enter image description here

I really want to know if the problem is from the script used in Calculate Value or from my Date Expression or from Select Layer By Attribute knowing that Selection Type is NEW_SELECTION ??

• Vince: Have to tried displaying whereClause to the console? It looks like it would be a SQL syntax error, just like the error message states.
• Sefiane: I tried to displaying it but it's not enable... maybe because it's the output for calculate value!!
• Moh: Did you tried to insert value in the format (DD/MM/YYYY) as your field shows
• Sefiane: Yes, i tried it byt the same error
• fatih_dur: You can use Collect Values "model only tool" to see what your Calculate_Value tool yields.

Answer 1

What I can see from the script, it is designed to produce WHERE clauses for string and number fields. This FAQ page suggests adding date term before your dates. I would suggest this addition to your code:

import arcpy
def buildWhereClauseMultiValue(table, field, values):
 """Takes a semicolon-delimited list of values and constructs a SQL WHERE
 clause to select those values within a given field and table."""
 # Add DBMS-specific field delimiters
 fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(table).path, field)
 # Split multivalue at semicolons and strip quotes
 valueList = [value[1:-1] if (value.startswith("'") and value.endswith("'")) else value for value in values.split(';')]
 # Determine field type
 fieldType = arcpy.ListFields(table, field)[0].type
 # Add single-quotes for string field values
 if str(fieldType) == 'String':
 valueList = ["'%s'" % value for value in valueList]
 elif str(fieldType) == 'Date':
 valueList = ["date '%s'" % value for value in valueList]
 # Format WHERE clause in the form of an IN statement
 whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(valueList))
 return whereClause `

As a last note, as User2009 suggested, use dd/mm/yyyy format since your locale is set to that.

• Sefiane: Hi @fatih_dur , yes that what i thought to maybe i need to add date term but i tried your suggestion and i got this error... please check it again if i need to change something in it ====> ERROR 000539: Runtime error SyntaxError: 'return' outside function (<string>, line 24) Failed to execute (Calculate Value).
• fatih_dur: Can you please confirm indentation of your code is correct? This error says your return statement is outside of the def, meaning you terminate the function and then using return.
• Sefiane: Sorry i don't understand python, but all i know is that when i used the first script it worked without showing error in 'return' but when i added ** elif str(fieldType) == 'Date': valueList = ["date '%s'" % value for value in valueList] ** to # Add single-quotes for string field values it show this error
• fatih_dur: See my update on answer.
• Sefiane: Hi @fatih_dur, it's good now... Thanks a lot. ( By the way, if i would like to extract only where value is NULL, what i should write in the Value option ? )

Answer 2

I think your date entry should be something like:

Date_de_To = date'2018-02-24 00:00:00'

enter image description here

• Sefiane: I tried it bro but it's not working, maybe because i didn't mentioned date field in the script ...