Copying into empty fields until...

[cochi30]

I have a table (imported from Excel), that has one field that I need to fix; there are about 10,000 records. The column (field) has a date followed by records of tickets that were worked on for that day until another date is encountered and followed by tickets of that day.
What I want to do is to have another column that has that date in the adjacent fields by each ticket number.
I want to copy that date until the next date is encountered in the loop and copy the new date and so on....I need a vba code for a loop that would do that...
Example...
original field New Field to copy
2/2/2009 2/2/2009
P3333222 2/2/2009
p3332221 2/2/2009
p3332220 .....
p3332340
2/3/2009 2/3/2009,
p3334566 etc...
p4443344


p2233334
2/4/2009 2/4/2009
p.....
p....
p....

[RuralGuy]

Do all of your ticket numbers start with "p", or at least an alpha character?

[cochi30]

Do all of your ticket numbers start with "p", or at least an alpha character?

No, but I can make them have a "p"

[RuralGuy]

You can guarantee that the field contains either a date or a string that starts with "p"? Then we can come up with a conversion procedure.

[RuralGuy]

Your procedure is going to look something like:
<<< WARNING - AIR 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!

[cochi30]

I can find many uses for this procedure at work. This current project I have, I was given a spreadsheet with 10,000 lines like that and they wanted a chart but I needed to format it correctly.
Field name changes and table name change noted
Thanks!!

[RuralGuy]

Glad I could help.

[cochi30]

ruralguy,
I processed the procedure and although it worked, it placed the current date -
Field1 Field2
3/12/2009 3/12/2009
P398050 6/7/2009
P398056 6/7/2009
P398083 6/7/2009
P398086 6/7/2009
P398106 6/7/2009
P398097 6/7/2009
P398130 6/7/2009
3/13/2009 3/13/2009
P398235 6/7/2009
P398246 6/7/2009
P398244 6/7/2009
P398036 6/7/2009
P398230 6/7/2009
P398231 6/7/2009
P398278 6/7/2009
P398292 6/7/2009
P398229 6/7/2009
P398324 6/7/2009
3/16/2009 3/16/2009
P398452 6/7/2009
P398464 6/7/2009

[RuralGuy]

Hmm...try changing just the part in RED.

         If Not InStr(!Field1, "p") Then
            '-- This is not a ticket value
            If Not IsDate(CDate(!Field1)) Then
               '-- Not a valid date either - use today??
               LastDate = Date
            Else
               LastDate = CDate(!Field1)
            End If
         End If