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 :

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 :

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

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

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

fatih_dur · Accepted Answer · 2018-05-12T09:10:23.057

0

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.

edited May 12 '18 at 09:10

answered May 11 '18 at 15:05

fatih_dur

4,983
2
16
35

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 (, line 24) Failed to execute (Calculate Value). – Sefiane May 11 '18 at 21:11
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. – fatih_dur May 11 '18 at 22:30
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 – Sefiane May 11 '18 at 22:43
See my update on answer. – fatih_dur May 12 '18 at 09:26
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 ? ) – Sefiane May 12 '18 at 11:24

score 0 · Answer 2 · answered May 11 '18 at 15:08

0

I think your date entry should be something like:

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

answered May 11 '18 at 15:08

Moh

1,588
1
9
17

I tried it bro but it's not working, maybe because i didn't mentioned date field in the script ... – Sefiane May 11 '18 at 21:16

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

2 Answers2