Sort by two criteria where one may be null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I do this? (Or is it even possible?)

I have two fields on the form--startdate and enddate.

I would like sort the form the following way:
First: by startdate where enddate is null
Next: by startdate where enddate is not null

This is so the user will see the 'active' entries before the inactive ones.

Thanks in advance.
 
I'm sorry, I don't understand and it did not work.

Here's what I typed...
SELECT FROM Main Order By IIf([startdate] Is Null, 0,1) , [startdate]

and Access says "The SELECT statement includes a reserved work or an
argument that is misspelled or missing, or the punctuation is incorrect."
 
It's because of the space in your table name (never recommended!)

If you can't rename your table, enclose the table name in square brackets.
As well, I believe Ofer made a typo, since you wanted to know whether
enddate was null, not startdate:

SELECT FROM [Main Order] By IIf(IsNull([enddate]), 0,1) , [startdate]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


sneagle said:
I'm sorry, I don't understand and it did not work.

Here's what I typed...
SELECT FROM Main Order By IIf([startdate] Is Null, 0,1) , [startdate]

and Access says "The SELECT statement includes a reserved work or an
argument that is misspelled or missing, or the punctuation is incorrect."

Select * from TableName Order By IIf([startdate] Is Null, 0,1) , [startdate]
--
Click to expand...
Click to expand...
 
Try this

SELECT * FROM [Main Order] By IIf([enddate] Is Null, 0,1) , [startdate]

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



sneagle said:
I'm sorry, I don't understand and it did not work.

Here's what I typed...
SELECT FROM Main Order By IIf([startdate] Is Null, 0,1) , [startdate]

and Access says "The SELECT statement includes a reserved work or an
argument that is misspelled or missing, or the punctuation is incorrect."

Select * from TableName Order By IIf([startdate] Is Null, 0,1) , [startdate]
--
Click to expand...
Click to expand...
 
Sorry, try this

SELECT [Main].* FROM [Main] Order By IIf([enddate] Is Null, 0,1) , [startdate]

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



sneagle said:
I'm sorry, I don't understand and it did not work.

Here's what I typed...
SELECT FROM Main Order By IIf([startdate] Is Null, 0,1) , [startdate]

and Access says "The SELECT statement includes a reserved work or an
argument that is misspelled or missing, or the punctuation is incorrect."

Select * from TableName Order By IIf([startdate] Is Null, 0,1) , [startdate]
--
Click to expand...
Click to expand...
 
Excellent. Thanks to all. One question, how does this work?
SELECT [Main].* FROM [Main] Order By IIf([enddate] Is Null, 0,1) , [startdate]

I mean, how does Access interpret the IIf statement?

P.S. I did not have a space in my table name, I for the post I made up a
table name
 
IIf(expr, truepart, falsepart)
So, in the query we checked if the end date = null, if yes we returned 0 and
if it is different the null the iif returned 1
So that is the first sort
0
0
0
1
1
1
Instead of the end date, and then we sorted by the start date, so the start
date will start a new sort when the first sort will change from 0 to 1
-
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
 
Back
Top