MS Access Order by X unless Y < 0 then order by Z

Question

I've got a bit of an issue in Access whereby I need to order by one column containing time remaining (ORDER BY Round([Days].[DRem],2) but if the number falls below 0 , I need to order by a different column containing text.

The current query is (UPDATED):

SELECT 
    SR.SRNum, 
    Round([qSLADays].[SLDaysRemaining],2) AS SLADRem, 
    SR.SrFreeText
FROM 
    (
        SR 
        INNER JOIN 
        qSLAHours 
            ON SR.RowID = qSLAHours.RowID
    ) 
    INNER JOIN 
    qSLADays 
        ON SR.RowID = qSLADays.RowID
WHERE 
    (
        (
            (Round([qSLADays].[SLDaysRemaining],2))>=0 
                And (Round([qSLADays].[SLDaysRemaining],2))<=1.5
        ) 
        AND 
            ((SR.SRStatus) In ("Open","Resolution Identified")) 
        AND 
            ((SR.SRSubstatus) In ("Assigned","Technical Action","Subject Expert Action","Active Investigation")) 
        AND 
            ((SR.Team)="SWx PAS Support MILL")
    ) 
    OR 
    (
        ((SR.SRSubstatus) In ("Assigned","Technical Action","Subject Expert Action","Active Investigation")) 
            AND ((SR.SrFreeText) Like "PRIORITY*")
    )
ORDER BY Round([qSLADays].[SLDaysRemaining],2) DESC;

I need to Order by SLADRem where SLADRem >0 DESC else order by Freetext ASC but can't see any way to logically do this.

Appreciate any help

I can't add any images of sample output or sample output as I'm unable to add an image. However, anything greater than 0 invariably has no freetext so I firstly need to sort on anything greater than 0. Anything less than 0 needs to be sorted by the freetext field which contains a priority number defined as "PRIORITY0X" E.G.:

 0.93
 0.52
 0.49
 0.16
-6.66 PRIORITY07
-7.34 PRIORITY02
-7.94 PRIORITY09
-8.32 PRIORITY01
-9.15 PRIORITY05

Answer 1

One way to handle cases like this is to include a derived column in your query that you can sort on. In your case you could create such a column that included something along the lines of:

• one character to control the order of "batches": the positives followed by the negatives,
• a nine-digit numeric string to control the sorting for the "positive" batch of records, and
• some number of characters from the text field to control the sorting of the "negative" batch

For example, for the sample data [SampleData]

SampleID  SLADrem  Freetext 
--------  -------  ---------
       1     3.14  whatever1
       2     5.00  whatever2
       3     4.20  whatever3
       4     0.00  bravo    
       5    -1.23  alpha    
       6     1.00  whatever4
       7    -2.00  charlie  

the query

SELECT 
    IIf(SLADRem>0, "A" & Format(999999 - SLADRem, "000000.00"), "B000000.00") & Left(Freetext, 50) AS SortKey, 
    *
FROM SampleData
ORDER BY 1;

returns

SortKey              SampleID  SLADrem  Freetext 
-------------------  --------  -------  ---------
A999994.00whatever2         2     5.00  whatever2
A999994.80whatever3         3     4.20  whatever3
A999995.86whatever1         1     3.14  whatever1
A999998.00whatever4         6     1.00  whatever4
B000000.00alpha             5    -1.23  alpha    
B000000.00bravo             4     0.00  bravo    
B000000.00charlie           7    -2.00  charlie