Maximum Function in VBA (2 Viewers)

[brian7268]

Does VBA really not have an easy built in function for maximum value? I know in excel you can use the worksheet function, but that doesn't apply to Access.

I have 4 dates in a record and I want VBA to find the maximum date. I would think that would be very easy, but I can't find anything.

Any suggestions?

 

[plog]

In a query, with a properly laid out database, getting the maximum value of a range of dates is trivial:

SELECT MAX(DateField) AS RecentDate FROM YourTableNameHere;

But no, Access doesn't have a function you can plug 4 dates into and get the maximum, however, you could create your own in a module. Or, as I suspect, you can normalize your tables and use the query I posted.

Why do you have 4 date fields in the same record and what are the field names?

 

[Beetle]

Well, you could use the Max function in a query;

Select Max(SomeField) As MaxValue From SomeTable;

Or, you could use the domain function DMax in code or in the control source od a control on a form or report;

DMax("SomeField", "SomeTable")

You can find more info on these in Access help.

 

[brian7268]

In my mind, the SQL wouldn't work if they are in the same record, but I could just not be thinking of it right.

The data has a field for Date_Reported, Date_Confirmed, Date_Notified, and Date_Resolved. Each entry has a date in each column. I want the macro to find the Maximum Date (i.e. the latest one).

 

[mdlueck]

The data has a field for Date_Reported, Date_Confirmed, Date_Notified, and Date_Resolved. Each entry has a date in each column. I want the macro to find the Maximum Date (i.e. the latest one).

For that you would need to step through each pair, and have the flow of the code always select the farthest out of the two being compared, ultimately to arrive at the one farthest out date.

 

[JHB]

The data has a field for Date_Reported, Date_Confirmed, Date_Notified, and Date_Resolved. Each entry has a date in each column. I want the macro to find the Maximum Date (i.e. the latest one).

Per record or for all data?

 

[Galaxiom]

It can be done using nested IIFs. However, wouldn't the order of the Report, Confirm, Notify and Resolve always be the same?

Moreover the need to compare values in multiple fields often suggests the values should be stored as separate records in a related table.

In this structure the related table would contain the following fields:
Foreign Key to connect the record to the main records.
An EventType field eg Report, Confirm, Notify, Resolve. (Usually stored as an ID with the text in a lookup table.)
A date value.

This allows the dates to be compared in a query, including grouping by the keys to show the maximum date for each main record.

This structure also allows any number of events for each main record and any number of new EventTypes to be added to the system without changing the strucure of the tables. The events are displayed in a subform or subreport.

Queries will be very much faster than using the nested IIFs.

 

[gemma-the-husky]

what you want is quite easy to write, but is not intrinsic to access, because it is not a usual database-type function.

if you have an assortment of dates that you may need to compare, then your table design is possibly not the best.

instead of

job, date1, date2, date3, date4

you could possibly have a sub table

job, datetype, date - with up to 4 rows in it

now a simple dmax will find the largest value of all these dates.

----
a general compare function for 4 dates

function datemax (d1,d2,d3,d4) as date
dim d as date
d = nz(d1,0)
if nz(d2,0)>d then d = d2
if nz(d3,0)>d then d = d3
if nz(d4,0)>d then d = d4
datemax=d
end function

note that this returns a date of 0 (30/12/1899) if all dates are blank. it soesn;t tell you which date was biggest.

 

[Pat Hartman]

Brian, your request implies that the dates are a repeating group and since a repeating group violates first normal form, there would never be a function in a relational database to work with them. This has nothing to do with Acces. It has to do with relational theory which applies to all relational databases. As the others have said, either normalize the data or write your own function.