1

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

  • 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 KACHOUANI May 11 '18 at 12:11
  • 1
    Did you tried to insert value in the format (DD/MM/YYYY) as your field shows – Moh May 11 '18 at 12:20
  • Yes, i tried it byt the same error – Sefiane KACHOUANI May 11 '18 at 12:25
  • You can use Collect Values "model only tool" to see what your Calculate_Value tool yields. – fatih_dur May 11 '18 at 15:04
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.

  • 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). – Sefiane KACHOUANI 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 KACHOUANI May 11 '18 at 22:43
  • See my update on answer. – fatih_dur May 12 '18 at 9: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 KACHOUANI May 12 '18 at 11:24
0

I think your date entry should be something like:

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.