Sort by two criteria where one may be null

[sneagle]

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.

 

[sneagle]

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."

 

[Douglas J Steele]

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!)

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]
--

 

[Ofer]

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

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]
--

 

[Ofer]

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

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]
--

 

[sneagle]

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

 

[Ofer]

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