Solved

Query using dates to determine on time record

Posted on 2011-09-02
11
300 Views
Last Modified: 2013-11-05
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
Comment
Question by:SteveL13
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Expert Comment

by:c1nmo
ID: 36473608
Nested IIFs?

iif([DateShpd]<=iif([Ack_Date]>[ReschDate],[Ack_Date],[ReschDate]),"Y","N")
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 36473621
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36473628
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36473651
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
 

Author Comment

by:SteveL13
ID: 36473705
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36473751
My function will handle the possible NULL value in [ReschDate]
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36473754
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36473771
Sorry, did not mean to suggest that fyed's suggestion will not handle nulls.  It will, of course :)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36473784
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
 

Author Closing Comment

by:SteveL13
ID: 36473833
Works perfectly.  Thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36473838
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now