SelectLayerByAttribute_management issue, need help

[JohnRitsko]

Trying to work my way through a PGDB and update the field PROJECT_STATUS depending on data in other fields.  My problem seems to be in the query portion of theelectLayerByAttribute.  I've tried to many iterations of this.  Eventually I wanted to update this data directly in SDE on our SQL Server but I think I can figure that out once I get this.  Thank you..

BELOW IS A WORKING SCRIPT.  Thank you all.

#### updated script
# Import arcpy module
import arcpy, time, sys
from arcpy import env

env.overwriteOutput = True
env.workspace = "Database Connections//GISSDE.RTC.sde"
arcpy.MakeFeatureLayer_management("RTC.GISSDE.MPO_CMWG_L_TEST","cmwg_lyr")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_DESIGN_START] = 1")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"DESIGN\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_NTP] = 1")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"CONSTRUCTION\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_SUBSTANTIAL_COMPLETION] <= CURRENT_TIMESTAMP")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"SUBSTANTIAL COMPLETION\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "RTC_PROJECT_CLOSED <= CURRENT_TIMESTAMP")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"CLOSED\"", "VB", "")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[PROJECT_STATUS] IS NULL OR [PROJECT_STATUS] = ''")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"PLANNED\"", "VB", "")

[JohnRitsko]

import arcpy
from arcpy import env
env.overwriteOutput = True
env.workspace = r"C://test//CMWGp.mdb"
arcpy.MakeFeatureLayer_management("C://test//CMWGp.mdb//CMWG_L_TEST","cmwg_lyr")
arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_DESIGN_START] ='1'")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"DESIGN\"", "VB", "")

Here is what I tried and no luck.

Traceback (most recent call last):

  File "C:\Users\ritskoj\Desktop\cmwgUpdates.py", line 19, in <module>

    arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_DESIGN_START] ='1'")

  File "C:\ArcGIS\Desktop10.2\arcpy\arcpy\management.py", line 6688, in SelectLayerByAttribute

    raise e

ExecuteError: ERROR 000358: Invalid expression

Failed to execute (SelectLayerByAttribute).

[DarrenWiens2]

What is the field type for [ACTUAL_DESIGN_START?

[JohnRitsko]

Short Integer

The others are Date and Text.

[DarrenWiens2]

Remove the quotes around number values.

[JohnRitsko]

You sir have fixed my issue.  Thank you.  Both you and Wes Miller have been very helpful.

  Now I do have one other questions.  How do I deal with a date fields?

[MichelleTotman]

Hi John,

Have you considered using if/elif/else statements in the CalculateField_management code block instead of the SelectLayerByAttribute_management function?

Here is an example:

arcpy.CalculateField_management(layer, "Project_STATUS", expression="update(!ACTUAL_DESIGN_START!, !ACTUAL_NTP!)", "PYTHON", """def update(design, NTP):

   if design == 1:

       return "Design"

   elif NTP == 1:

       return "Construction"

   else:

       pass

""")

[JohnRitsko]

Michelle,

  Yeah, I wanted to deal with this in and if elif situation but couldn't get my expressions working right.  Now that I've got things rolling and once I figure out how to query a date I'll be set.

On that note, if you know how to ask for the current date and check against that I would be very grateful.

Thank you all for the help.

[curtvprice]

Within python scripts it really makes more sense to use an update cursor instead of the Calculate Value tool with a code block.

fields = ["Project_status", "ACTUAL_DESIGN_START", "ACTUAL_NTP"]
with arcpy.da.UpdateCursor(layer, fields) as rows:
  for row in rows:
      if row[1] == 1:
          row[0] = "Design"
      elif row[2] == 1:
          row[0] = "Constructions"
      rows.updateRow(row)

[MichelleTotman]

Thanks, Curtis! I'll have to give that a try next time.

[JohnRitsko]

Things are progressing well.  The main answer to my question has been answered but I'm having issues with this last bit in regards to the date.  I can't seem to query against the data, Invalid Expression.  Any thoughts?

arcpy.SelectLayerByAttribute_management("cmwg_lyr", "NEW_SELECTION", "[ACTUAL_SUBSTANTIAL_COMPLETION] <= CURRENT_DATE")
arcpy.CalculateField_management("cmwg_lyr", "PROJECT_STATUS", "\"SUBSTANTIAL COMPLETION\"", "VB", "")