Date Difference to Return the most Current Date 
Author Message
 Date Difference to Return the most Current Date

Hi Everyone-
I am hoping someone out there can help me.
I am trying to take various dates and return the most
current date.
I have two tables.  One (Contract) has the Original Date.
Two (Modifications to the Contract) has the New dates that
range depending if it was modified.
What I am trying to do is to take the Original Date and
the New dates and have it return the most current date out
of the two automatically on the form.
Any ideas?


Sat, 19 Nov 2005 03:11:14 GMT  
 Date Difference to Return the most Current Date
If you trying to find which date is the closest to the current date, of
which I presume you are, why not use the "Date" function to return the
system's current date (which should be correct unless the developer is
testing something on purpose with the system date and has the system date
set to some other date/time)

In VBA, for:

Sub pcdClosestDate(Index as Long)
    'Closest future date will have the index value of 1
    'Closest past date will have the index value of 2
    'Closest date regardless if it's future or past will have the index
value of 3
    Dim lngDateDiff as Long

    If Index = 1 Then
        If OriginalDate >= Date Then
            lngDateDiff = OriginalDate - Date
        Else
            lngDateDiff = -1
        End If
    ElseIf Index = 2 Then
        If OriginalDate <= Date Then
            lngDateDiff = Date - OriginalDate
        Else
            lngDateDiff = -1
        End If
    ElseIf Index = 3 Then
            lngDateDiff = ABS(OriginalDate - Date)
    Else
    End If

You could then use either a While...Wend or Do[ While]...Loop[ Until], or
maybe even a For Each...Next loop depending on how you are doing it, the
grouping, if any, of the data, and use your comparison.

Within your looping pattern, you would first have the date be checked to see
if it meets the requirement to be used for comparison purposes [in the cases
of Index value of 1 or 2].  If it does meet, then have it check to see if
lngDateDiff = -1 and if it does, then assign the diff to the variable,
lngDateDiff.  If not, then take the diff of the date and today, then compare
that diff to the value in lngDateDiff, and if lngDateDiff is greater than
the value that the code just calculated, then lngDateDiff gets the new
value.  Once all records are compared, you then just calculate the most
current date by the formula of:

MostRecentDate = Date + lngDateDiff

Of course, an alternative to doing it this way is to use SQL with it's
grouping and the few grouped info capabilities.  Example of this would be:

Pull in your 2 tables and join them by a common field such as a sales order
number field.

Put in your criterias such as if you want the most current date that's in
the past, you would put in your filter to exclude all records that involved
future dates.

Group the results by Sales Order Number

Have it shows the most recent date by which of the 3 methods:

    Most current past date:  Criteria is no future dates, show max date of
the field within the grouped data.
    Most current future date:  Criteria is no past dates, show min date of
the field within the grouped data.
    Most current date regardless if it's future or past:  No criteria, an
additional field would need to be created to calculate the date diff, which
then this would still leave 2 possible dates unless the datediff is '0'.

As you can see, SQL works out very well for the first 2 ways, but not quite
so easily for the third method.  My general experience of working with SQL
and VBA is as follows:

SQL is very efficient in pulling the data that you need from various tables
and DBs, and even in the order that you need it with the proper grouping,
but it lacks some analytical tools, which then VBA can take over the data to
do further analytical work on the data and get it in a format that is much
needed for the user(s) of the information to be able to read and understand
it.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Quote:
> Hi Everyone-
> I am hoping someone out there can help me.
> I am trying to take various dates and return the most
> current date.
> I have two tables.  One (Contract) has the Original Date.
> Two (Modifications to the Contract) has the New dates that
> range depending if it was modified.
> What I am trying to do is to take the Original Date and
> the New dates and have it return the most current date out
> of the two automatically on the form.
> Any ideas?



Sun, 20 Nov 2005 00:58:07 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Date/Time picker and calculating date differences

2. VBA date query - not getting current date

3. Changing a Calculate field (Date) depending on Current date

4. Any code for current date to Julian date samples

5. date range to current date at specific time syntax question

6. Q:get current date and then create date range

7. vb6 date variables comparing with access date fields returns always empty recordset

8. DATES ISSUE: Number of the current Week of the current Month

9. Displaying the Current Date and Current Time

10. Date - date = date

11. Compare date in form with date/time in form with date/time in database

12. Dates dates dates

 

 
Powered by phpBB® Forum Software