Calculated field in query isn't working as expected

[alikona]

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:

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:

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:

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:

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?

[June7]

Have you step debugged? I expect cause is related to dates as string or Null or empty string passed in the array.

Your calculated dates are strings, not true date values. That can cause an issue in comparing date values: 12/11/2022 will not be greater than 12/9/2022

Won't the date always be same or higher in each subsequent field? Just pull value from last field with data. Advise not to allow empty string in tables or in calculated field. Consider:

Nz([Pic_Report], Nz([3rd], Nz([2nd], [1st])))

[alikona]

Have you step debugged?

Admittedly I know how to debugg in the VBA screens but I am unaware on how to do so through query functions.

Won't the date always be same or higher in each subsequent field? Just pull value from last field with data. Advise not to allow empty string in tables or in calculated field. Consider:

Nz(Pic_Report, Nz([3rd], Nz([2nd], Nz([1st], Null))))

The Pic_Report field kind of throws this all off. the 1st/2nd/3rd would go in order, but the Pic_Report could be at any time after the 1st notification, or not at all. Perhaps I could build a more complex IIF statement to evaluate 1st/2nd/3rd vs any value in Pic_Report.

[June7]

Doesn't matter where function is called from - query, textbox, vba - debugging is same. Set a break point and step through code.
I tested your function by calling from VBA immediate window.

So Pic_Report could have a date value between any of the other 3 fields? Basically, could just send two values to function:

Nz([3rd], Nz([2nd], [1st])) and [Pic_Report]

However, still an issue of comparing string dates.
Change field calculations to use Null instead of empty string.

Simple Function:

Function GetMaxDate(dte1 As Date, dte2 As Date) As Date
GetMaxDate = IIf(dte2 > dte1, dte2, dte1)
End Function

Called from query:

GetMaxDate(CDate(Nz([3rd], Nz([2nd], [1st]))), CDate(Nz([Pic_Report],0))

All assumes there will always be a date in at least [1st].

This calculation can probably be done in query without VBA.

[alikona]

Thank you, that works as expected.