Display newest of two fields (one Max, one Min)

[cherithmoore]

I need to calculate how long people have been on a waiting list.
They are considered to be on the waiting list if they meet this criteria: 1) Assessment greater than 60 days ago and 2) No cancellation within the past 60 days.

I am able to calculate those values using a series of queries and end up with a table that shows MinOfAssessment Date and MaxOfCancelled Date for those who meet the criteria.

I want a third field that displays the newest of these two dates. I have tried this:

WL StartDate: IIf([MaxOfCancelled Date] Is Null, [MinOfAssessment Date],[MaxOfCancelled Date])



It doesn't work. I know I had this working in an earlier iteration of the db, but now I cannot repeat it. Ideas? I also want to write an expression that tells me how long the person has been on the waiting list and used:

Days on WL: Date()-[WL Start Date]

That works in some queries, but not the one that contains the wait list as I have described it, with the min and max column. I think my problem has to do with not being able to add a third calculated column but I can't find a way around this.

[jzwp11]

Have you tried the following?:

WL StartDate: IIf(IsNull([MaxOfCancelled Date]), [MinOfAssessment Date],[MaxOfCancelled Date])


For the difference of two dates, you want to use the datediff() function

Days on WL: datediff("d", Date(),[WL Start Date])

The above will return the number of days between the two dates. If you end up with a negative value just swap the positions of the two date fields.

[cherithmoore]

Thanks for replying.

The expression you provided does not work in the query, but I tried turning my query into a "make table" query and used the resulting table in yet another query that included the expression. It worked.

Now, I'm concerned that I will need to run all those supporting queries to make sure that WL table is up to date. I'm wondering if my "make table" work around is a bad idea.


StartDate: IIf(IsNull([MaxOfCancelled Date]),[MinOfAssessment Date],[MaxOfCancelled Date])

[jzwp11]

Having another table with the same data that is already in another table violates normalization rules. I'm wondering why it worked in one query and not the other.

Can you provide the SQL text of the original query that did not work?

[cherithmoore]

This is it:

SELECT [Clients - Active - No Provider - Assessment > 60 - No Cancel <60].[Client Full Name], [Clients - Active - No Provider - Assessment > 60 - No Cancel <60].[MinOfAssessment Date], [Clients - Active - Cancellation Newest].[MaxOfCancelled Date], IIf([MaxOfCancelled Date] Is Null,[MinOfAssessment Date],[MaxOfCancelled Date]) AS [WL StartDate]
FROM [Clients - Active - No Provider - Assessment > 60 - No Cancel <60] LEFT JOIN [Clients - Active - Cancellation Newest] ON [Clients - Active - No Provider - Assessment > 60 - No Cancel <60].[Client Full Name] = [Clients - Active - Cancellation Newest].[Client Full Name];

[jzwp11]

Out of curiosity, does the query run correctly if you remove the IIF() function entirely?

If it does, then I would save the query without the IIF() and then create another query that includes the IIF(). For example if I save the query without the IIF() function as Q1, my second query would look like this

SELECT Q1.[Client Full Name], Q1.[MinOfAssessment Date], Q1.[MaxOfCancelled Date], IIf(IsNull(Q1.[MaxOfCancelled Date],Q1.[MinOfAssessment Date],Q1.[MaxOfCancelled Date]) AS [WL StartDate]
FROM Q1

[cherithmoore]

And, after months of cleaning data and struggling with this database I have the information I need!! Thank you!!

[jzwp11]

You're welcome.