ArcGIS Desktop – Solving ERROR 000358: Invalid Expression in Select Layer By Attribute

arcgis-desktoparcpyerror-000358modelbuilderselect-by-attribute

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 ??

Best Answer

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.

Related Question