Adjusting times in a Table

[GoSenators]

Hi There! I'm a newbie to VBA so thanks in advancefor any help on this. I'm trying to iterate through records in a table and adjust times for records meeting certain criteria. Below is what I have so far: Any suggestions on how to get it to work would be great!

Option Compare Database

Sub SampleTimeFix()

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim datetime As Date

DoCmd.RunSQL "DELETE * FROM DataPidTimeFix"
Set rs = CurrentDb.OpenRecordset("SELECT *,format(sampletime,'hh:nn:ss') as [Time], format(sampletime,'mm/dd/yyy') as [Date] from Pidmaster", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("DataPIDTimefix", dbOpenDynaset)

datetime = rs!sampletime

Do While Not rs.EOF

If serialnumber = 100974 And (Format(sampletime, "mm/dd/yyyy")) = "12/10/2007" And Station = "ppbStation6" Then
datetime = DateAdd("nn", -56, rs!sampletime)

ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "12/11/2007" And Station = "ppbStation6" Then

datetime = DateAdd("nn", -58, rs!sampletime)

ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "12/12/2007" And Station = "ppbStation6" Then

datetime = DateAdd("nn", -73, rs!sampletime)
ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "12/13/2007" And Station = "ppbStation6" Then

datetime = DateAdd("nn", -79, rs!sampletime)
ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "12/14/2007" And Station = "ppbStation7" Then
datetime = DateAdd("nn", -79, rs!sampletime)

ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "2/11/2007" And Station = "ppbStation2" Then
datetime = DateAdd("nn", -34, rs!sampletime)

ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "2/12/2007" And Station = "ppbStation2" Then
datetime = DateAdd("nn", -63, rs!sampletime)

ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "2/13/2007" And Station = "ppbStation1" Then
datetime = DateAdd("nn", -28, rs!sampletime)

ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "2/14/2007" And Station = "ppbStation1" Then
datetime = DateAdd("nn", -26, rs!sampletime)

ElseIf serialnumber = 100974 And Format(sampletime, "mm/dd/yyyy") = "2/15/2007" And Station = "ppbStation2" Then
datetime = DateAdd("nn", -33, rs!sampletime)

rs2.Update

Else
datetime = rs!sampletime
End If

rs.MoveNext
Loop
End Sub

 

[genomon]

Can't tell exactly what you are after here, but for starters, "datetime" is a reserved word in Access, and cannot be used as a variable.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[SkipVought]

Also Date is a reserve word and should not be used for field headings

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[MajP]

Do not loop. Build a function and use it in a query. Then use the query in an update to update the records in DataPIDTimeFix

Public Function timeFix(dtmSampleTime As Date, lngSerialNo As Long, strStation As String) As Date
  If lngSerialNo = 100974 Then
    Select Case Int(dtmSampleTime) ' return only the date portion
      Case #12/10/2007#
        If strStation = "ppbStation6" Then
            timeFix = DateAdd("nn", -56, dtmSampleTime)
        End If
       Case #12/11/2007#
        If strStation = "ppbStation6" Then
            timeFix = DateAdd("nn", -58, dtmSampleTime)
        ElseIf strStation = "ppbStation2" Then
            timeFix = DateAdd("nn", -34, dtmSampleTime)
        End If
      Case #12/12/2007#
        If strStation = "ppbStation6" Then
            timeFix = DateAdd("nn", -73, dtmSampleTime)
         ElseIf strStation = "ppbStation2" Then
            timeFix = DateAdd("nn", -63, dtmSampleTime)
        End If
      Case #12/13/2007#
        If strStation = "ppbStation6" Then
            timeFix = DateAdd("nn", -79, dtmSampleTime)
        ElseIf strStation = "ppbStation1" Then
            timeFix = DateAdd("nn", -28, dtmSampleTime)
        End If
      Case #12/14/2007#
        If strStation = "ppbStation7" Then
            timeFix = DateAdd("nn", -79, dtmSampleTime)
        ElseIf strStation = "ppbStation1" Then
            timeFix = DateAdd("nn", -26, dtmSampleTime)
        End If
      Case #12/15/2007#
        If strStation = "ppbStation2" Then
            timeFix = DateAdd("nn", -33, dtmSampleTime)
        End If
    End Select
  End If
 End Function

test this first outside of a function by passing literals to it and see if it returns the correct time. Then use it in a query the query should use a criteria where serial number = 100974, and then I could of got rid of the outside if then.

 

[MajP]

If it is a date, work with a date instead of converting it to a string. A date has an integer portion representing the date and a decimal portion representing time.

To prove that:
?now
3/20/2008 4:14:01 PM

?cdbl(now)
39527.6792708333

?now = #3/20/2008#
False

?int(now) = #3/20/2008#
True

?clng(#3/20/2008#)
39527

?clng(int(now()))
39527