Query using dates to determine on time record
I have a query (see snapshot) which I have added a field to (ReschDate). The query has been used to determine if an order was delivered on time or not. Currently the "OnTime" field looks at "DateShpd" and compares it to "Ack_Date". If DateShpd" is <= Ack_Date then the answer is Y.
But now we want to compare "DateShpd" to EITHER "Ack_Date" OR "ReschDate" using the higher valued date from "Ack_Date" or "ReschDate". So if DateShpd is <= the higher of the values, Ack_Date or ReschDate" the the answer should be "Y". If not then the answer should be "N".
How can I rework this query to handle this?
But now we want to compare "DateShpd" to EITHER "Ack_Date" OR "ReschDate" using the higher valued date from "Ack_Date" or "ReschDate". So if DateShpd is <= the higher of the values, Ack_Date or ReschDate" the the answer should be "Y". If not then the answer should be "N".
How can I rework this query to handle this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Switch to your SQL view, and change your WHERE clause to something like this:
WHERE IIf(Nz(Ack_Date, 0) >= Nz(ReschDate, 0), Nz(Ack_Date, 0), ReschDate) Between Forms![Name of form]![name of start date] And Forms![Name of form]![name of end date]
The Nz is in there in case there is any possibility of those date columns containing nulls.
WHERE IIf(Nz(Ack_Date, 0) >= Nz(ReschDate, 0), Nz(Ack_Date, 0), ReschDate) Between Forms![Name of form]![name of start date] And Forms![Name of form]![name of end date]
The Nz is in there in case there is any possibility of those date columns containing nulls.
Note that in c1nmo's suggestion http:#a36473608, if Ack_Date is not null but ReschDate is null, the whole thing is going to evaluate to false, because any logical comparison between a null and a non-null is always false.
Of course, if those columns are not nullable, then there is no need to worry.
:)
Of course, if those columns are not nullable, then there is no need to worry.
:)
ASKER
I have not tried any of the suggestion yet but I did forget to mention that "Ack_Date" will always have a value. In some records "ReschDate" will not have a value. Therefore, “On Time” is Y if "DateShpd" is on or before the latest of those two dates and N if after both dates.
Which suggestion will deal with the null "ReschDate" issue?
--Steve
Which suggestion will deal with the null "ReschDate" issue?
--Steve
My function will handle the possible NULL value in [ReschDate]
My suggestion in http:#a36473628 will handle nulls. If Ack_Date can never be null, it can be simplified a bit:
WHERE IIf(Ack_Date >= Nz(ReschDate, 0), Ack_Date, ReschDate) Between Forms![Name of form]![name of start date] And Forms![Name of form]![name of end date]
WHERE IIf(Ack_Date >= Nz(ReschDate, 0), Ack_Date, ReschDate) Between Forms![Name of form]![name of start date] And Forms![Name of form]![name of end date]
Sorry, did not mean to suggest that fyed's suggestion will not handle nulls. It will, of course :)
Aside to fyed: you might be interested in my RowStats function, which behaves similarly to yours, except that it can do all of the typical aggregations:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1775-Computing-row-wise-aggregations-in-Access.html
:)
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1775-Computing-row-wise-aggregations-in-Access.html
:)
ASKER
Works perfectly. Thanks.
Patrick,
I've seen it and like it but I've been using my fnMin and fnMax for about 10 years and for most of my stuff they are sufficient.
I've seen it and like it but I've been using my fnMin and fnMax for about 10 years and for most of my stuff they are sufficient.
iif([DateShpd]<=iif([Ack_D