Determining minimum date in recordset

  • Thread starter Thread starter David
  • Start date Start date
D

David

QCDate Field1 Field2 Field3 Field 4 MinDate
1/21/04 2/12/04 3/10/04

Objective: Look at each field and compare to determine
which is minimum and put it in the MinDate field. Here is
how I am going about it but doesn't seem to work.

Dim db as dao.database
Dim rst as dao.recordset
Dim tempDate as Date
set db = currentdb
set rst = db.openrecordset("mytable", dbopendynaset)

Do Until rst.eof
If Not isnull(rst!QCDate) then
tempDate = rst!QCDate
If Not IsNull(rst!field1) and rst!field1<=tempDate then
tempDate = rst!field1
If Not IsNull(rst!field2) and rst!field2<=tempDate
then
tempDate = rst!field2
...
...
rst.Edit
rst!MinDate = tempDate
rst.Update
rst.MoveNext
Loop
rst.close
set rst = nothing

This does not seem to be giving me the accurate results.

Has anyone ever incurred this scenario, and if so, how
did
you resolve it.

Thanks
DJ
 
The fact that you have multiple date fields in a single row makes me wonder
whether your table is properly normalized. Rather than have multiple columns
in a single row, it's more common to use a second table, and store each
similar value as a separate row.

In any case, the problem with your function is that tempDate doesn't get
reset as you read each row. You assign a value for tempDate for the first
row, but unless there's a date in the second row that's less than that
already assigned value, tempDate isn't going to get reset.

Try

Do Until rst.eof
If Not isnull(rst!QCDate) then
tempDate = 0
If Not IsNull(rst!QCDate) and rst!QCDate <=tempDate then
tempDate = rst!QCDate
End If
If Not IsNull(rst!field1) and rst!field1<=tempDate then
tempDate = rst!field1
End If
If Not IsNull(rst!field2) and rst!field2<=tempDate then
tempDate = rst!field2
End If
...
...
rst.Edit
rst!MinDate = tempDate
rst.Update
rst.MoveNext
Loop

Note that I'm initalizing tempDate to 0, which corresponds to 30 Dec, 1899.
If there's a chance that any of your dates are going to be before that date,
use a different value.

Another option is to write a function that takes five variants in, and
returns the least value. (You need to use variants to allow for the
possiblity of Null)

Function SmallestOfFive(Field1 As Variant, _
Field2 As Variant, Field3 As Variant, _
Field4 As Variant, Field5 As Variant) As Variant

If Not IsNull(Field1) Then
SmallestOfFive = Field1
End If
If Not IsNull(Field2) Then
If Field2 < SmallestOfFive Then
SmallestOfFive = Field2
End If
End If
If Not IsNull(Field3) Then
If Field3 < SmallestOfFive Then
SmallestOfFive = Field3
End If
End If
If Not IsNull(Field4) Then
If Field2 < SmallestOfFive Then
SmallestOfFive = Field4
End If
End If
If Not IsNull(Field5) Then
If Field3 < SmallestOfFive Then
SmallestOfFive = Field5
End If
End If

End Function

Now, you can simply write an Update query that sets your MinDate field equal
to the value returned by that function for each row.
 
Back
Top