Query with 2 dates

[MunroeM]

Hello all,



I have a Query that has the following fields: Item Name, Retest Date and Expiration Date.

I have a sub form on my main form that lists all the Item names and retest dates in order from current date to the future. I now need to add the Expiration date in there also but it only shows up at the top of the list if it is past the expiration date.

example: (assume todays date is 7 Sept 16 for this example)
Name - retest date - exp date
Item-A 1-Jan-17 3-Sept-16
Item-B 8-Sept-16 23-Feb-18
Item-C 12-Sept-16 5-Dec-17
Item-D 6-Oct-16 9-Jan-22

So the list is sorting retest dates in order from now to future and if an expiration date is past todays date it will move it to the top of the list even if the retest date is further in the future then the ones on the top of the list.

Any idea how I can do that. I have tried to add the expiration date on the query with a criteria of <=Date() but the list is blank as there is nothing expired right now but I still need the retest dates to show up like before

Thanks for the help

[Bulzie]

Confused on what you are asking. How do you want it to sort? Is the example above how you want it to work or how it is working now and incorrect? Are you sorting each field in your query? Are all 3 of those fields in the same table?

[MunroeM]

How it is currently working is its sorting the retest dates in ascending order and just displaying the expiration dates beside the retest dates.

How I would like it to work is the retest dates are still in ascending order BUT if an expiration date is past the current date I want it to pop to the top of the list. In the example you can see Item B,C, and D are sorting according to the retest date BUT Item-A is at the top of the list because the Expiration Date is past the current date

All fields are in the same table.

Hope that clears up what I'm asking.

[pbaldy]

Add a field to the query:

IIf(ExpDate<=Date(), 1,2)

and have the primary sort on that field.

[MunroeM]

I put the IIf([ExpDate]<=Date(), 1,2) in the criteria section of the ExpDate field but nothing will show up when the query runs.

What does the 1 and 2 do for the true and false statements in the IIF?

This is my SQL statement for the query if that helps

SELECT ItemTbl.[ItemName], ItemTbl.[RetestDate], ItemTbl.[ExpDate]
FROM ItemTbl
WHERE (((ItemTbl.[ExpDate])=IIf([ExpDate]<=Date(),1,2)))
ORDER BY ItemTbl.[RetestDate], ItemTbl.[ExpDate];

[pbaldy]

I didn't say add it to the criteria, I said "add a field". More like:

SELECT ItemTbl.[ItemName], ItemTbl.[RetestDate], ItemTbl.[ExpDate], IIf([ExpDate]<=Date(),1,2) As SortField
FROM ItemTbl
ORDER BY IIf([ExpDate]<=Date(),1,2), ItemTbl.[RetestDate], ItemTbl.[ExpDate];

[MunroeM]

BAM!!! that worked like a charm.

Thanks for the help

[pbaldy]

Happy to help!