ORDER BY date doesn't work inside IIF Statement 1

[Mike555]

I have a query as shown below which select a field named "DueDate" and then orders by "DueDate". This query is used to import data into Microsoft Excel, and due to the fact that Excel doesn't handle the Nz function correctly when importing data, I had to format the IIF statement as shown instead of just using an Nz function to reformat NULL values.

SELECT DISTINCT OrderID, IIf(IsNull([tblOrder.DueDate]),"No Date",([tblOrder.DueDate])) AS DueDate
FROM tblOrder
ORDER BY IIf(IsNull([tblOrder.DueDate]),"No Date",([tblOrder.DueDate]));

The problem is that using the IIF statement sorts the results incorrectly. For example...

My expected results are:
OrderID DueDate
1234 04/4/05
2345 04/5/05
3456 04/25/05

But the actual results are:
OrderID DueDate
3456 04/25/05
1234 04/4/05
2345 04/25/05

If I just select and sort by the "DueDate" field without formatting it with the IIF, it sorts correctly. But for some reason the IIF throws off the sort. How can this be fixed? Thanks!


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[alvechurchdata]

IIf(IsNull([tblOrder.DueDate]),"No Date",([tblOrder.DueDate]))

Add this to the query as a new column named (say) DateOrder and then ORDER BY DataOrder.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike555]

Geoff, If I understand this correctly then the new syntax would look something like this, correct?

SELECT DISTINCT OrderID, IIf(IsNull([tblOrder.DueDate]),"No Date",([tblOrder.DueDate])) AS DateOrder
FROM tblOrder
ORDER BY DateOrder;

I've made this modification, but now when the query runs it produces the error "ORDER BY clause (DateOrder) conflicts with DISTINCT."

Thanks.


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[PHV]

And this ?
SELECT OrderID, IIf(IsNull([DueDate]),"No Date",[DueDate]) AS DueDate
FROM tblOrder
ORDER BY 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

I would think this:

[tt]ORDER BY IIf(IsNull([tblOrder.DueDate]),"No Date",([tblOrder.DueDate]));[/tt]

coerces the return from the IIF to be string, so all is sorted as a string, even if I can't quite make it fit with your sample. Try:

[tt]ORDER BY IIf(IsNull([tblOrder.DueDate]),Date()+1000,([tblOrder.DueDate]));[/tt]

Or a subtract, or a date litteral #1/1/2200# or something, according to the requirements.

Roy-Vidar

 

[Mike555]

PHV, 'ORDER BY 2' still sorted the results incorrectly.

RoyVidar, I tried to modify the ORDER BY clause as you suggested, but then I receive the error msg "ORDER BY clause (DateOrder) conflicts with DISTINCT." It seems that the fields in the ORDER BY clause need to match the fields exactly as they appear in the SELECT DISTINCT statement. Could you explain a bit further what you mean by "Or a subtract, or a date litteral #1/1/2200# or something, according to the requirements."

Thanks all for your help.


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[RoyVidar]

Sorry, didn't see the Distinct.

Date()-1000 - subtraction
#1/1/1900# - date litteral

Then your second column is for display purposes, you could either add a third column to the select, or do the formatting in the form/report and keep it at two columns. PHV's suggestion, with a little alteration should do.

[tt]SELECT OrderID, IIf(IsNull([DueDate]),Date()+1000,[DueDate]) AS DueDate
FROM tblOrder
ORDER BY 2[/tt]

Roy-Vidar

 

[Mike555]

RoyVidar, Nevermind... I know understand what you are saying. Basically you're saying that if the IIF statement is true(the object isnull), then don't return the string "No Date" but instead return a date so that everything is formatted as a date.

I did this and the sort is now working correctly. Thanks for your help!


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.