Solved

# Query using dates to determine on time record

Posted on 2011-09-02
288 Views
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?
0
Question by:SteveL13
• 5
• 3
• 2
• +1

LVL 6

Expert Comment

ID: 36473608
Nested IIFs?

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

LVL 47

Accepted Solution

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
``````
0

LVL 92

Expert Comment

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

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

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 47

Expert Comment

ID: 36473751
My function will handle the possible NULL value in [ReschDate]
0

LVL 92

Expert Comment

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

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

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

ID: 36473833
Works perfectly.  Thanks.
0

LVL 47

Expert Comment

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

### Suggested Solutions

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.