Split query results by whether a certain field is null

[George-Bowyer]

I am running on too much caffeine and too little sleep at the moment, and I can't work out whether what I am trying to do is really complicated or whether I'm just missing something really obvious...?

I have a table which tracks positions held by volunteers within various organisations. Each record holds a PeopleID from tblPeople, an OrganisationID from tblOrganisations and a PositionID from tblPositions.

Each record also has start date and retire date fields.

I want to be able to list all of the positions by current (fldRetireDate is null or in the future) first, in a certain order (fldOrder) and then the retired positions (fldRetireDate is not null and in the past) in the same order.

I do not want to sort them by fldRetireDate


Here is my current sql:

SELECT tblOrganisationPositions.fldOrgPosPosition, [fldLastName] & ", " & [fldFirstName] AS fldContactName, tblPositions.fldPosition, tblOrganisationPositions.fldOrgPosOrg, tblPeople.PeopleID, tblPositions.fldOrder, & _
 tblOrganisationPositions.fldOrgPosPerson, tblOrganisationPositions.fldStartDate, tblOrganisationPositions.FldRetireDate
FROM tblPositions INNER JOIN (tblPeople INNER JOIN (tblOrganisations INNER JOIN tblOrganisationPositions ON tblOrganisations.OrganisationID = tblOrganisationPositions.fldOrgPosOrg) ON tblPeople.PeopleID = tblOrganisationPositions.fldOrgPosPerson) ON tblPositions.PositionID = tblOrganisationPositions.fldOrgPosPosition
WHERE (((tblOrganisations.fldOrgType)=2))
ORDER BY tblPositions.fldOrder, tblPeople.fldLastName, tblPeople.fldInitial;

Can someone please offer some guidance?


Many thanks

George

 

[June7]

Then you need to have a field that serves as a group identifier for the current/null and past values and use that as an order by criteria. This field can be calculated with an IIf() expression.

ORDER BY IIf(Nz([fldRetireDate],Date())>=Date(), 1, 2), tblPositions.fldOrder, tblPeople.fldLastName, tblPeople.fldInitial;

 

[George-Bowyer]

Oh, wow. That is awesome Does exactly what I want it to

Thanks.

I knew it must be doable, but I hadn't got a clue how to do it.

Now I just need to analyse what it actually does and how it works - but I think I'll sleep first...