Solved Converting a MAXIFS formula to DMAX Access Expression

[allout80]

Hello all,

I'm looking to see if the following Excel formula can be converted into a expression in Access. Specifically, converting MAXIFS() statement into a DMAX().

Essentially, the formula is saying if the "Task Status" column is equal to "Pending" then the value equals "1/1/2050". However if not, then I want to find the max value of the "Completion date" (Column L:L) given the following two criteria.

Criteria 1: The "Notification" number must equal the notification number of the current row.
Criteria 2: The "Custom Task Code Text" must equal the Custom Task Code Text of the current row.

L:L (Completion Date)
A:A (Notification)
AD:AD ("Custom Task Code Text")

I'm not sure if this is even possible but thanks for the help!

 

[arnelgp]

IIF([StatusField] = "Pending", #1/1/2050#,
DMax("FieldLL", "YourTable", "Field_AA = '" & [theNotification] & "' And Field_AD) = '" & [theCustomTaskCode] & "'"))

 

[plog]

I would do this with a subquery to get the MAX value for every completion date for every Notification/TaskCode combination:

SELECT MAX[CompletionDate] AS LastCompletionDate, Notification, TaskCode
FROM YourTableNameHere
GROUP BY Notification, TaskCode

Save that as a query, then bring it into the query you are working on--JOIN it appropriately--Notification to Notification, TaskCode to TaskCode and then make your IIF field:

YourFieldName: IIF([StatusField] = "Pending", #1/1/2050#, LastCompletionDate)

It's best to avoid domain functions like DMax in queries.

 

[arnelgp]

It's best to avoid domain functions like DMax in queries.

but using Domain function makes the resulting Query Updateable.

 

[allout80]

IIF([StatusField] = "Pending", #1/1/2050#,
DMax("FieldLL", "YourTable", "Field_AA = '" & [theNotification] & "' And Field_AD) = '" & [theCustomTaskCode] & "'"))

I tried this and it's resulting in a syntax error.

Here are the actual inputs I used

[StatusField] = [Task_Status]
"FieldLL" = "Completion Date"
"Your Table" = "Query_Dash"
"Field_AA" = "Notification"
[theNotification] = [Notfication]
Field_AD = "Custom Task Code Text"
[theCustomTaskCode] = [Custom Task Code Text]

 

[allout80]

I would do this with a subquery to get the MAX value for every completion date for every Notification/TaskCode combination:

SELECT MAX[CompletionDate] AS LastCompletionDate, Notification, TaskCode
FROM YourTableNameHere
GROUP BY Notification, TaskCode

Save that as a query, then bring it into the query you are working on--JOIN it appropriately--Notification to Notification, TaskCode to TaskCode and then make your IIF field:

YourFieldName: IIF([StatusField] = "Pending", #1/1/2050#, LastCompletionDate)

It's best to avoid domain functions like DMax in queries.

Thanks! I'll give this a shot

 

[allout80]

Thanks! I'll give this a shot

Is it going to be a issue that the "Task Code" field is actually an expression in the query I'm working on?

 

[allout80]

I would do this with a subquery to get the MAX value for every completion date for every Notification/TaskCode combination:

SELECT MAX[CompletionDate] AS LastCompletionDate, Notification, TaskCode
FROM YourTableNameHere
GROUP BY Notification, TaskCode

Save that as a query, then bring it into the query you are working on--JOIN it appropriately--Notification to Notification, TaskCode to TaskCode and then make your IIF field:

YourFieldName: IIF([StatusField] = "Pending", #1/1/2050#, LastCompletionDate)

It's best to avoid domain functions like DMax in queries.

SELECT MAX(Query_Dash.Completion Date) AS LastCompletionDate, Notification, TaskCode
FROM Query_Dash
GROUP BY Notification, TaskCode

This is giving a syntax error

 

[allout80]

SELECT MAX(Query_Dash.Completion Date) AS LastCompletionDate, Notification, TaskCode
FROM Query_Dash
GROUP BY Notification, TaskCode
View attachment 99154
This is giving a syntax error

SELECT MAX([Query_Dash].[Completion Date]) AS LastCompletionDate, Notification,[Query_Dash].[Custom Task Code Text]
FROM Query_Dash
GROUP BY [Query_Dash].[Notification], [Query_Dash].[Custom Task Code Text];

I messed on the syntax but that's fixed. However here is the result

 

[allout80]

Pulling for the query didn't work but the table did!