Max Date in a Query

  • Thread starter Thread starter Van T. Dinh
  • Start date Start date
V

Van T. Dinh

1. Your Table Structure is incorrect. You have 20
repeating groups of Field (MailingDates) and probably not
all of them are used for all Records. The correct design
is to separate the MailingDates out to a separate Table.

Read up on Relational Database Design Theory and Database
Normalisation and restructure your Table(s) accordingly. T

2. A ***temporary*** solution (since you will have other
problems later with the current Table Structure) is to use
the fnMax. Copy the posted code into a Standard Module:

'****
Public Function fnMax(ParamArray varValue() As Variant)
Dim varMax As Variant
Dim intIndex As Integer

On Error GoTo fnMax_Err
varMax = varValue(0)
For intIndex = 1 To UBound(varValue())
If varValue(intIndex) > varMax Then
varMax = varValue(intIndex)
End If
Next intIndex

fnMax = varMax

fnMax_Exit:
Exit Function

fnMax_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & _
Err.Description & vbCrLf & vbCrLf & _
"(Programmer's note: vbaMathematics.fnMax)" &
vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnMax_Exit
End Function
'****

Save the Module with a unique name different from "fnMax".

Create a calculated Field in your Query:

MostRecent:fnMax(Date1, Date2, ..., Date20)

HTH
Van T. Dinh
MVP (Access)
 
Can't claim the credit for this. The idea came from
someone else and I simply modifed it to my style of coding.

Cheers
Van
 
The only thing that would concern me with your function is - Does it handle
Nulls?

Testing shows it does AS LONG AS the first Value in the array is not a null. One
way to handle that would be to force a very large negative number. I might use
the following line in place of the line immediately following On Error GoTo fnMax_Err.

varMax = Nz(varValue(0), -1E+20)

Of course that will lead to an error if ALL the values should be null, so you
might even use something like the following modification to handle that case.

:


Public Function fnMax(ParamArray varValue() As Variant)
Dim varMax As Variant
Dim intIndex As Integer

On Error GoTo fnMax_Err
varMax = -1E+20
For intIndex = 0 To UBound(varValue())
If varValue(intIndex) > varMax Then
varMax = varValue(intIndex)
End If
Next intIndex

If varMax = -1E+20 then
fnMax = Null
else
fnMax = varMax
End If

fnMax_Exit:
Exit Function

fnMax_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & _
Err.Description & vbCrLf & vbCrLf & _
"(Programmer's note: vbaMathematics.fnMax)" _
& vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume fnMax_Exit
End Function
 
Thanks, John.

I had this fn a while ago and before I posted, I was wondering the same and
actually tested for some Null values but never as the first entry of the
ParamArray.
 
Back
Top