I've simplified my question in the thread “Variable Grouping by Date” hoping to get some guideance.
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.
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.
