Way of referring to a previous row in a table using vba?

[ChewDinCompSci]

I am trying to refer to a previous row in a table to compare two times (time1 and time2). Is there a way of referring to time1 after dealing with time2 in vb so that I can get the difference between the two times? Any help anyone can provide is much appreciated.


MatChew
codename: vba4dumbE

 

[JeffChr]

I think some clarification is needed. Are time1 and time2 fields in a table or variables in code? And how are time1 and time2 set?

 

[ChewDinCompSci]

To be specific, I need to refer to two times (TimeIn and TimeOut). Since, the calculation I need to make is only for reporting purposes I suppose I can refer to the time values in my table. Therefore, if TimeOut of the current record minus TimeIn of the previous record is less than 30 then ConTime = True (ConTime is another column in my table which will store the difference between the two times). Is this clear?

MatChew
codename: vba4dumbE

 

[mik18]

You can use a recordset to retreive the TimeIn of the previous record.

Dim qry as String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim v_TimeIn As ???
Set cnn = CurrentProject.Connection

qry = "SELECT TimeIn FROM [i]TableName[/i] WHERE [i]criteria match[/i] = [i]??[/i]"

rst.open qry,cnn, adOpenStatic, adLockReadOnly

v_TimeIn = rst.Fields("TimeIn")

rst.Close
cnn.Close

If TimeOut - v_TimeIn < 30 Then
    Return True
Else
    Return False
End If

mike

 

[ChewDinCompSci]

Thanks a lot mike! I'll try that.

MatChew
codename: vba4dumbE

 

[ChewDinCompSci]

Quick Question Mike. In the criteria match in the SQL you've added should the criteria match be something general so that all TimeIn values from the table are selected? Or, do I need a unique identifier here cuz if so there wouldn't be one.

MatChew
codename: vba4dumbE

 

[mik18]

The WHERE Clause would need a unique identifier to know which record to pull the TimeIn field from.
If it is in the previous row then you could return the entire dataset, use the rst.Find command to move to the current record, then use the rst.MovePrevious command. Hope this helps.

mike

 

[HitechUser]

ChewDinCompSci,

Maybe this will get you closer...

http://support.microsoft.com/default.aspx?scid=kb;en-us;210236

Good Luck