• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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? Snapshot
0
SteveL13
Asked:
SteveL13
  • 5
  • 3
  • 2
  • +1
1 Solution
 
c1nmoCommented:
Nested IIFs?

iif([DateShpd]<=iif([Ack_Date]>[ReschDate],[Ack_Date],[ReschDate]),"Y","N")
0
 
Dale FyeCommented:
I use a function for this.

OnTime: iif([DateShpd] < fnMax([Ack_Date], [ReschDate]), "Y", "N")

The advantage of the function is that it will take as many parameters as you want to pass it, and it automatically ignor NULLs.
Public Function fnMax(ParamArray ValList() As Variant) As Variant

   Dim intLoop As Integer
   Dim myVal As Variant
   
   For intLoop = LBound(ValList) To UBound(ValList)
      If Not IsNull(ValList(intLoop)) Then
         If IsEmpty(myVal) Then
            myVal = ValList(intLoop)
         ElseIf ValList(intLoop) > myVal Then
            myVal = ValList(intLoop)
         End If
      End If
   Next
   fnMax = myVal
   
End Function

Open in new window

0
 
Patrick MatthewsCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Patrick MatthewsCommented:
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.

:)
0
 
SteveL13Author Commented:
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
0
 
Dale FyeCommented:
My function will handle the possible NULL value in [ReschDate]
0
 
Patrick MatthewsCommented:
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]
0
 
Patrick MatthewsCommented:
Sorry, did not mean to suggest that fyed's suggestion will not handle nulls.  It will, of course :)
0
 
Patrick MatthewsCommented:
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1775-Computing-row-wise-aggregations-in-Access.html

:)
0
 
SteveL13Author Commented:
Works perfectly.  Thanks.
0
 
Dale FyeCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now