Solved

Query using dates to determine on time record

Posted on 2011-09-02
11
288 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 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

10 Experts available now in Live!

Get 1:1 Help Now