Using IIF function in the ORDER BY statement in a Query (1 Viewer)

[Salwa]

I came across the following syntax in a query

ORDER BY TEST.EMP_ID, IIf(IsNull([ETPSTARTDATE]),[DATE_HIRE],[ETPSTARTDATE]);

Appreciate if someone can explain to me how this works, with examples. I was unaware that you could do this.

Thanks heaps

sample data

EMP_ID	DATE_HIRE	ETPSTARTDATE	EMP_STATUS
21214​	24/11/2008		A1
21216​	24/11/2008		A1
21217​	23/11/2008	23-Nov-08	A1
21219​	1/12/2008		A1
21226​	9/02/2009		A1
21229​	1/02/2009		A1
21230​	23/02/2009		A1
21233​	2/03/2009		A1
21235​	2/03/2009		A1
21236​	23/03/2009		A1
21239​	14/04/2009		A1
21240​	1/06/2009		A1
21245​	9/06/2009		A1
21251​	8/06/2009		A1
21253​	5/10/2009		A1
21254​	24/08/2009		A1
21255​	24/08/2009		A1
21256​	24/08/2009		A1
21257​	7/09/2009		A1
21261​	5/10/2009		A1
21262​	12/10/2009		A1
21267​	23/01/1984	23-Jan-84	A1
21270​	30/11/2009		A1

 

[Gasman]

If ETPSTARTDATE is null, then use DATE_HIRE, else use ETPSTARTDATE
However as you are sorting first by EMP_ID and they appear unique, the secondary field appears to be useless?

 

[Salwa]

If ETPSTARTDATE is null, then use DATE_HIRE, else use ETPSTARTDATE
However as you are sorting first by EMP_ID and they appear unique, the secondary field appears to be useless?

Thanks for the prompt response.

I am BA trawling through SQL queries and VBA code to reverse engineer requirements.

I was unclear as to how you can change the sort condition midway through.

How would it work for the following?

EMP_ID	DATE_HIRE	ETPSTARTDATE	EMP_STATUS
21214​	24/11/2008		A1
21214​	22/11/2008		A1
21214​	23/11/2008	23-Nov-08	A1
21219​	1/12/2008		A1
21226​	9/02/2009		A1
21229​	1/02/2009		A1

 

[Gasman]

You are not changing the sort condition midway through, just defining the value for the second sort field.
For 2214 they would then be sorted by date.
If you were to include the field that holds the iif() expression you would see that.

 

[plog]

W3C schools is a great place to learn about SQL:

SQL ORDER BY Keyword

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

The ORDER BY is a list of fields that you want to sort your query by and the order of those fields in the ORDER BY is important. ORDER BY operates left to right--a field to the left takes precedence of fields to the right. Think of multiple fields as tie breakers--if there's a tie in the value in the first ORDER BY field, the ORDER BY uses the second to break that tie, and then the third and fourth, etc.

You've provided a pretty poor example because you only have 1 field that has a NULL ETPSTARTDATE and that date is the exact same as its DATE_HIRE. So let me make a better sample:

EMP_ID	DATE_HIRE	ETPSTARTDATE
21214	24/11/2008	20/11/2008
21214	23/11/2008	30/11/2008
21214	22/11/2008
21219		2/2/2007
21219	9/2/2009	1/1/2007
21219

Mentally think of it like so: The above is the result of all other SQL clauses running except the ORDER BY. It has all the data it needs but now it needs to put the records in the prescribed order.

ORDER BY TEST.EMP_ID, IIf(IsNull([ETPSTARTDATE]),[DATE_HIRE],[ETPSTARTDATE]).

Since there's an IIF in the ORDER BY by it can be helpful to think of the SQL engine adding a temporary field to every record that only it will see and use. So now, to the computer your data looks like this:

EMP_ID	DATE_HIRE	ETPSTARTDATE	IIF
21214	24/11/2008	20/11/2008	20/11/2008
21214	23/11/2008	30/11/2008	30/11/2008
21214	22/11/2008		22/11/2008
21219		2/2/2007	2/2/2007
21219	9/02/2009	1/1/2007	1/1/2007
21219

Now imagine the computer allocates in memory a new blank query the exact size of the above one, but without any records. Now the computer takes the first record and puts it in the blank query. It then takes the second record and uses the ORDER BY to either place it before or after that first record. Then it takes the third record and places it correctly, etc etc. When there's a tie in EMP_ID it uses the IIF column above to break those ties.

Eventually that once blank query looks like this:

EMP_ID	DATE_HIRE	ETPSTARTDATE
21214	24/11/2008	20/11/2008
21214	22/11/2008
21214	23/11/2008	30/11/2008
21219
21219	9/02/2009	1/1/2007
21219		2/2/2007

 

[ebs17]

For technical implementation: It is also clearer to simply leave out unnecessary brackets.

IIf(IsNull(ETPSTARTDATE), DATE_HIRE, ETPSTARTDATE)

To run the query in Access, it is easier and cleaner to use Nz.

Nz(ETPSTARTDATE, DATE_HIRE)

In cases where the query is called outside of Access (Excel, VBScript, etc.), the Expression Service does not work, which means that functions such as Nz and IsNull are not available. There you should limit yourself to pure Jet SQL syntax:

IIf(ETPSTARTDATE Is Null, DATE_HIRE, ETPSTARTDATE)

The check for Is Null will also be somewhat faster against IsNull because it corresponds to the in-house syntax on Jet and does not have to use the external VBA function.