Query Record Calculation--Determining second record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that is sorted by descending date. I would like to calculate
the difference between the largest date and the second largest date (the
first and second records). Is there a way to do this in VB? Or is there a
way to identify the 2nd largest?
 
This function will return the date differnent between the two records.

Function GetDateDiff()
Dim MyDB As Database, MyRec As Recordset, MyLargDate As Variant
Set MyDB = CodeDb
Set MyRec = MyDB.OpenRecordset("SELECT MyTable.MyDateField FROM MyTable
ORDER BY MyTable.MyDateField DESC")
If not MyRec.eof then
MyLargDate = MyRec!MyDateField
MyRec.MoveNext
GetDateDiff = DateDiff("d", MyRec!MyDateField, MyLargDate)
End if
End Function
 
Thanks for the help! One other question though. Can you call a function
from a query? I would call the function in one query (that has the machine
and date information), then the function would run a query for that machine
up to that date and perform the date difference. The date difference would
be a field in the initial query.

Basically I want to call the function for each record in a query. Can that
be done?

Thanks again!
 
Yes, you can call a function from a query, but create the function in a module.
You can also send a parameter with the function and filter the date for each
mechine

Function GetDateDiff(MechineNum as long)
Dim MyDB As Database, MyRec As Recordset, MyLargDate As Variant
Set MyDB = CodeDb
Set MyRec = MyDB.OpenRecordset("SELECT MyTable.MyDateField FROM MyTable
Where [MechineNumField] = " & MechineNum & " ORDER BY MyTable.MyDateField
DESC")
If not MyRec.eof then
MyLargDate = MyRec!MyDateField
MyRec.MoveNext
GetDateDiff = DateDiff("d", MyRec!MyDateField, MyLargDate)
End if
End Function

But be carefull, if the mechine apear few time in the query, then you will
run the function to many time, and for no reason.
 
Thanks for the help. It took a while to work. I had to add a set of quotes
around the MechineNum for it to work.

""" & MechineNum & """



Ofer said:
Yes, you can call a function from a query, but create the function in a module.
You can also send a parameter with the function and filter the date for each
mechine

Function GetDateDiff(MechineNum as long)
Dim MyDB As Database, MyRec As Recordset, MyLargDate As Variant
Set MyDB = CodeDb
Set MyRec = MyDB.OpenRecordset("SELECT MyTable.MyDateField FROM MyTable
Where [MechineNumField] = " & MechineNum & " ORDER BY MyTable.MyDateField
DESC")
If not MyRec.eof then
MyLargDate = MyRec!MyDateField
MyRec.MoveNext
GetDateDiff = DateDiff("d", MyRec!MyDateField, MyLargDate)
End if
End Function

But be carefull, if the mechine apear few time in the query, then you will
run the function to many time, and for no reason.
Scott J. said:
Thanks for the help! One other question though. Can you call a function
from a query? I would call the function in one query (that has the machine
and date information), then the function would run a query for that machine
up to that date and perform the date difference. The date difference would
be a field in the initial query.

Basically I want to call the function for each record in a query. Can that
be done?

Thanks again!
Click to expand...
Click to expand...
 
Any suggestions on how to speed up the query? It runs fine until I add <=10
in the criteria for the field containing the GetDateDiff Function. It needs
to run for each record in the query (even if the machine appears twice, the
dates are different). Without the criteria, it runs fine, but with the
criteria, it takes a minute or two to load. Thanks for any suggestions.

Ofer said:
Yes, you can call a function from a query, but create the function in a module.
You can also send a parameter with the function and filter the date for each
mechine

Function GetDateDiff(MechineNum as long)
Dim MyDB As Database, MyRec As Recordset, MyLargDate As Variant
Set MyDB = CodeDb
Set MyRec = MyDB.OpenRecordset("SELECT MyTable.MyDateField FROM MyTable
Where [MechineNumField] = " & MechineNum & " ORDER BY MyTable.MyDateField
DESC")
If not MyRec.eof then
MyLargDate = MyRec!MyDateField
MyRec.MoveNext
GetDateDiff = DateDiff("d", MyRec!MyDateField, MyLargDate)
End if
End Function

But be carefull, if the mechine apear few time in the query, then you will
run the function to many time, and for no reason.
Scott J. said:
Thanks for the help! One other question though. Can you call a function
from a query? I would call the function in one query (that has the machine
and date information), then the function would run a query for that machine
up to that date and perform the date difference. The date difference would
be a field in the initial query.

Basically I want to call the function for each record in a query. Can that
be done?

Thanks again!
Click to expand...
Click to expand...
 
Back
Top