Comparisons using a changing value

[tallenwa]

I posted this on the Query forum originally, but it may belong here as it's a module that I'm struggling with.

I have a query [20 day vessel summary report query1] that finds the FirstOfBOL for each vessel in my table [20 day vessel Table]. I then have another query that has a module[MyGroup3] that passes each Vessel and BOL date and compares it to FirstOf recordset [20 day vessel summary report query1].

The module [MyGroup3] basically allows me to group these shipments based on their BOL date. [Mydte] is the date in my data table ([20 day vessel Table]), which I’m passing through in the query module [MyGroup3]. [dteVal] is the [FirstOf] date that I’m comparing [Mydte] to. The problem I can’t solve is that when I am out of the 20 day range, I need the [dteVal] to be set to the next date, for future comparisons.

I’ve tried to write an IF THEN to set the dteVal but have not been successful.

Within the [MyGroup3] module, here’s what I’m trying to do:
ii = Abs(DateDiff("d", myDte, dteVal))
If ii > 20 Then
'Change [dteVal] to [Mydte] (which is the next date) and use this date for future date range comparisons'
End If

Since I’m comparing [myDte] to a recordset, the only way I can figure to do this is by forcing the [myDte] value into the [dteVal] value in the recordset [20 day vessel summary report query1]. How can I do this, or is there a better work around for comparing the dates?

Thanks,
Travis

Here is a sample data set [20 day vessel Table]:

Vessel BOL [Mydte]
HANJIN 3/26/2004
HANJIN 3/30/2004
HANJIN 4/26/2004
HANJIN 4/26/2004
HANJIN 5/10/2004
HANJIN 5/20/2004
HANJIN 5/25/2004

The Firstof Query would be:
Vessel FirstofBOL [dteVal]
HANJIN 3/26/2004
SMITH 3/10/2004

So [dteVal] would be 3/26/2004 for the first two times through the function, then it would be 4/26 for the next 5 passes.

 

[denosaur]

Travis,

Try the following:

Sub Test20Day()
   
    Dim rs As Recordset
    Dim myVes As String
    Dim myDte As Date
    Dim dteVal As Date
    Dim ii As Integer
   
   Set rs = CurrentDb.OpenRecordset("SELECT * FROM [20 day vessel Table] ORDER BY Vessel,BOL")
   
   If rs.BOF And rs.EOF Then
      MsgBox "No records."
   Else
        dteVal = rs!BOL 'set the value of dteVal at first record
        Do While Not rs.EOF
            myVes = rs!Vessel
            myDte = rs!BOL  'set the value of myDte
            ii = Abs(DateDiff("d", myDte, dteVal))  'compare the date values
            If ii > 20 Then     'test the results
                dteVal = rs!BOL 'change the value of dteVal
            End If
            'do whatever else you want to do
            rs.MoveNext 'iterate through the records
        Loop
   End If

End Sub

HTH,
Dean

 

[tallenwa]

Thank you. I tried setting the dteVal before, but couldn't get it to work. I'm close, so maybe your code will help.

Travis