Using DATEADD function to previous row in field calculator

CharlottePeters — Mon, 04 Feb 2013 19:06:38 GMT

Hi, I have a feature class with several thousand points each with a start and end date/time field. A few of the points (stations) already have a designated start and end date/time. There are points between each station that need to have date/time values spaced a certain number of seconds apart based on the start time in the previous record (see attached). I've tried using the field calculator on the "start" field in both ArcGIS 9.3.1 and 10 using different variations of expressions I found on the web. Results have yielded the 1st date/time being repeated in subsequent records, increments of the year in serial format, or general errors.

Again I've tried different approaches, but below is a general idea of what I've been working off of in ArcGIS 9.3.1.

Static lastValue as variant
Dim Output as DATE
Dim pStartValue as DATE
Dim i as Long

pStartValue = #10/13/2012 4:28:23 PM#
i=2

If lastValue=pStartValue Then             [also tried "if IsNull(lastValue)", "if IsEmpty(lastValue)"]
Output = DATEADD("s",i,pStartValue)   [also tried "output=pStartValue"]
Else
Output = DATEADD("s",i,pStartValue)
End If
i=i+2                                               [also tried without incrementing the i value]
lastValue = Output

__esri_field_calculator_splitter__
Output

I will eventually be adding some seconds to the start field and putting the result in the end field, but that expression is pretty straightforward with the DATEADD() function. I've used an increment script before, but can't seem to adapt it to date/time values. Any help is appreciated. I don't look forward to copy/pasting/editing the date/time values for thousands of records and don't want to mess with the fields via MS Access.

Thanks, Charlotte

Re: Using DATEADD function to previous row in field calculator

CharlottePeters — Tue, 05 Feb 2013 23:49:12 GMT

I was able to figure out the expression I needed to add a certain number of time units to the previous time value.

Static t as integer                                  't will store the incremental time unit value
Dim startValue as date
Dim result as date

startValue = #10/12/2001 2:35:09 PM#      'needed # because my value is a date data type, I'll need to change
                                                           'this startValue for each preset date value listed at each station feature
t = t + 20                                           'the increment interval is set to 20
result = DateAdd("s", t, startValue)

[my field name] = result                           'I end up with a date/time value that is 20 seconds later than the previous date/time

It's time to do the happy dance!
Charlotte

P.S. Discovered that this calculation works as expected with times incrementing in the positive with each record if the calculation is done outside of edit mode. If the calcuation was done within edit mode, I ended up with times that incremented backwards with each successive record.

topic Re: Using DATEADD function to previous row in field calculator in Geoprocessing Questions

Using DATEADD function to previous row in field calculator

Re: Using DATEADD function to previous row in field calculator