Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query using dates to determine on time record

Posted on 2011-09-02
11
Medium Priority
?
343 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 49

Accepted Solution

by:
Dale Fye earned 2000 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 93

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36473751
My function will handle the possible NULL value in [ReschDate]
0
 
LVL 93

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 93

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 93

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 49

Expert Comment

by:Dale Fye
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

824 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