Date Computation Problem

[GordonV]

Is there an easy way to compare several dates within the same record and use the one that is the most recent in a Datedif expression?

Essentially I am trying to figure out if there is a date equivilent to Max() which can look at several fields within the same record.

I have looked through the archives but have not discovered the answer.

Thank you for your assistance.

Gordon

 

[raskew]

Just build your own function, e.g.:

'************************
Function theMax(thefirst As Date, thesecond As Date) As Date
'returns the greater of two date entries

theMax = IIf(thefirst >= thesecond, thefirst, thesecond)

End Function

'************************

If you have more than two dates to compare, you could use a variation of the following. I used Northwind's Orders table (which has three dates) as an example.

To test:

(1) Copy/paste the above function to a new module in Northwind.
(2) Copy/paste the following query-SQL to a new query in Northwind:


'************************
SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, themax([shippeddate],themax([OrderDate],[RequiredDate])) AS x FROM Orders;
'************************

If you look at the construction of X, hopefully you'll see that you could expand on this to include even more dates.

 

[MichaelRed]

Rather than putz around with how manty thisses and thats, just use a function designed to accomodate a variable number of args. I also built in the capability to deal with vaying data types (in different calls / instantations), so you can use the below to obtain the MAX of any number of items (of the same data type). This is (loosly) based on the intrinsic function "MAX" from languages like FORTRAN. It is left as an exercise for the ... to deveop the companion function [basMinVal]

Public Function basMaxVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MAXIMUM or a series of values

    Dim Idx As Integer
    Dim MyMax As Variant

    For Idx = 0 To UBound(varMyVals())
        If (varMyVals(Idx) > MyMax) Then
            MyMax = varMyVals(Idx)
        End If
    Next Idx

    basMaxVal = MyMax

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GordonV]

I know that I am showing my lack of experience with VBA but, I have not been able to get Michael's code to work yet. I have the function above set as a module and that stuck into the query I am using to do the computations.

varMyVals is set as the array of dates that I wish to compare.

I am sure that I have missed something obvious.

Any help would be appreciated.

Thanks,

Gordon

 

[raskew]

Michael Red's solution, an adaptation of the example provided under 'Parameter Arrays' in the Access Help File, is clearly superior to the one I provided, since it will process an infinite number of possibilities without modification.

Modifying the example previously provided, using the Orders table in Northwind, you could create this new query:

SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, themax([shippeddate],themax([OrderDate],[RequiredDate])) AS x, basMaxVal([shippeddate],[orderdate],[requireddate]) AS y
FROM Orders;

Note that the third line calls Michael's basMaxVal() function, specifying the three date fields as arguments.

 

[GordonV]

OK, I have scrutinized the code to determine if I was doing something incorrectly. I now understand what the code is doing. The code is saved as a module with the name basMaxVal.

In the query I am working on I have selected this function using the expression builder. The expression builder prompts me to define varMyVals. I replaced the <<varMyVals>> prompt with the fields that I wish to have compared.

The relevant expression appears as:
basMaxVal([Returned to Legal Date],[Returned to Legal Date2],[Returned to Legal Date3],[Returned to Legal Date4],[Returned to Legal Date5])

When I attempt to run the query I get the following error:
&quot;Undefined function 'basMaxVal' in expression.

After looking through a number of sources, I cannot figure out why this does not work.

Am I missing something obvious like this type of function can't be run in a query?

Any help would be appreciated.

Thanks,

GordonV

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,

Not really trying to get into the details, but some almagmation of your post and raskews should do it. I combined what I THIMK? (ipso cogito?) you should be doing:

SELECT Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate
basMaxVal([Orders].[Returned to Legal Date],[Orders].[Returned to Legal Date2],[Orders].[Returned to Legal Date3],[Orders].[Returned to Legal Date4],[Orders].[Returned to Legal Date5])
AS X
FROM Orders;

Although I am quite mystified as to you choice of 'x' as a more-or-less meaningless alais for the max date.



MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GordonV]

It still does not work.

I will try not to bog this thread down with unnecessary detail. Essentially I am trying to track the progress of a document through a series of procedural steps and then analyze the efficeny of the process by computing the elapsed days.

One stage of the process involves anywhere between 1 and 5 exchanges of a document with each exchange logged in a separate field. I need to do the date computation based on the most recent exchange. The function must be able to look at each of the five fields and select the most recent date. That date is then used in a DateDif function to count the number of days.

Michael's code appears to be exactly what is needed but I keep getting a &quot;Undefined function 'basMaxVal' in expression.&quot; error.

I have attempted to isolate the problem by testing code in a separate query using a modified version of the SQL provided above.

SELECT tblTrackingDates.[Returned to Legal Date], tblTrackingDates.[Returned to Legal Date2], tblTrackingDates.[Returned to Legal Date3], tblTrackingDates.[Returned to Legal Date4], tblTrackingDates.[Returned to Legal Date5], basMaxVal([tblTrackingDates].[Returned to Legal Date],[tblTrackingDates].[Returned to Legal Date2],[tblTrackingDates].[Returned to Legal Date3],[tblTrackingDates].[Returned to Legal Date4],[tblTrackingDates].[Returned to Legal Date5])
AS X
FROM tblTrackingDates;

But I get the same error.

The function is saved exactly as it appears above.

Why won't the system recognize the function? I do not believe that it is a problem with the code. This is in Access97 if it makes any diference.

At wits end-GordonV

 

[raskew]

I think there may be a misconception re modules.
Think of a module as a container which can hold
a variety of related or unrelated functions. To
see this in action, open up Northwind's Startup
module. You'll see several different functions,
each with a unique name (none of which is 'Startup').

I believe that Access will burp if a module name is
the same as a function name (not sure, have never
tried it), but it appears Micheal's basMaxVal() function
hasn't been properly saved. To fix, copy the function
to the clip board, create a new module, paste the
saved function to the module, and then save the
module as whatever the system suggests (e.g. Module1)
you could even use 'x' if you wanted to, it would
still work.

Then try rerunning your query. Please post back.

 

[GordonV]

As suggested I reinserted basMaxVal into a diferently named module. It now shows up as it should (one of potentially several functions within a module). However the error was the same.

I looked over some of the ParamArray examples in the help file and it appeared that there was an extra &quot;As Variant&quot; on the first line of Michael's code. I pasted Michael's code into the same module, changed the name, deleted the extra As Variant and IT WORKED! But when I went back to make the modifications in original code it did not work. I even tried pasting the new code over the old code and changing the name back to basMaxVal but it still did not work.

Here is the funny part. I changed the name of the code and it works just fine. I wonder if there is something else within Access triggered by the name basMaxVal? I cannot think of another explanation.

I am just thrilled that this works now.

Thank you MichaelRed & Raskew!!

 

[MichaelRed]

GordonV,

Check you app for another function with the same name. Public Procuedures MUST have a unique name within an application.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GordonV]

This procedure is the only public or private function in the entire DB. I don't know where else it could have came from.

At this point it works and I am grateful. I will opt not to muck around and cause a series of cascading problems.

GordonV