Your procedure is going to look something like:
<<< WARNING - AIR CODE >>>
Code:
Sub Foo()
'-- Examine Field1, if it is a date, make field2 that date.
'-- If it is a ticket number, make Field2 the last encountered date.
'original field New Field
' Field1 Field2
'2/2/2009 2/2/2009
'P3333222 2/2/2009
'p3332221 2/2/2009
'p3332220 ...
'p3332340 ..
'2/3/2009 2/3/2009
'p3334566 2/3/2009
'p4443344 2/3/2009
'p2233334 ...
'2/4/2009 2/4/2009
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim LastDate As Date
On Error GoTo Err_Foo
'-- Make sure we have a date in this variable
LastDate = Date
Set db = CurrentDb
Set Rs = db.OpenRecordset([YourTableName], dbOpenDynaset)
With Rs
Do While Not .EOF
.Edit
If Not InStr(!Field1, "p") Then
'-- This is not a ticket value
If Not IsDate(!Field1) Then
'-- Not a valid date either - use today??
LastDate = Date
Else
LastDate = CDate(!Field1)
End If
End If
'-- Now store the Last date
!Field2 = LastDate
.Update
.MoveNext
Loop
End With
Exit_Foo:
On Error GoTo 0
Rs.Close
Set Rs = Nothing
Set db = Nothing
Exit Sub
Err_Foo:
ErrMyPath:
MsgBox Err & ": " & vbCrLf & Err.Description
Resume Exit_Foo
End Sub
Will you be doing this often or is this just a one time gig? You need to change the names of the fields and the table to match your names. Work on a backup of the table first!