Hi all,
So I have a query that looks at 4 columns of dates, pulls the most recent date and adds 7 days in a calculated field. For whatever reason, it is skipping my last column.
I am using the following maximum function:
Code:
Function Maximum(ParamArray FieldArray() As Variant)' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I
' Return the maximum value found.
Maximum = currentVal
End Function
My Query SQL:
Code:
SELECT
qryNot.Status,
qryNot.ID,
qryNot.NotID,
qryNot.TagNo,
qryNot.NotNo,
qryNot.[1st],
qryNot.[2nd],
qryNot.[3rd],
qryNot.Pic_Report,
DateAdd("d",7,maximum([1st],[2nd],[3rd],[Pic_Report])) AS [Next Notification Due]
FROM
qryNot
GROUP BY
qryNot.Status,
qryNot.ID,
qryNot.NotID,
qryNot.TagNo,
qryNot.NotNo,
qryNot.[1st],
qryNot.[2nd],
qryNot.[3rd],
qryNot.Pic_Report;
The expression in my calculated field is:
Code:
Next Notification Due: DateAdd("d",7,maximum([1st],[2nd],[3rd],[Pic_Report]))
The columns I am looking at here are calculated fields in another query, this is the SQL for that query:
Code:
SELECT tblTags.Status,
tblTags.ID,
tblNotifications.NotID,
tblTags.TagNo,
tblTags.NotNo,
Max(IIf([tblNotifications].[NotID]=[tblTags].[ID],IIf([NotType]="1st",[NotDate],""))) AS 1st,
Max(IIf([tblNotifications].[NotID]=[tblTags].[ID],IIf([NotType]="2nd",[NotDate],""))) AS 2nd,
Max(IIf([tblNotifications].[NotID]=[tblTags].[ID],IIf([NotType]="3rd",[NotDate],""))) AS 3rd,
Max(IIf([tblNotifications].[NotID]=[tblTags].[ID],IIf([NotType]="Pic/Report",[NotDate],""))) AS Pic_Report
FROM tblTags INNER JOIN tblNotifications ON tblTags.ID = tblNotifications.NotID
GROUP BY
tblTags.Status,
tblTags.ID,
tblNotifications.NotID,
tblTags.TagNo,
tblTags.NotNo
HAVING
(((tblTags.Status)="Vendor Notified"));
As you can see in my photo attached, the [Pic_Report] column seems to be skipped in the calculation. Any ideas?