Sort Order based on specific criteria when 3 criteria are provided (1 Viewer)

Rx_ · Apr 7, 2016

See Attachment:
A SQL Statement receives criteria from the selections on two listboxes.
In the first two Fields
APD_FieldWorkdates is a PK long Autocounter
The ID_Wells is a FK long Autocounter

The query is based on a single table

Listbox 1 chooses a specific record with a known APD_FieldWorkDates and Know ID_Wells
Listbox 1 will always return 1 and only 1 record.

Listbox 2 only provides an ID_Wells. It will produce 0 to Many records

The problem is the sort order of the return.
The objective is to have the single record from listbox 1 always on Top.
The rest of the 0 to Many can be returned in any order.

The 0 to Many APD_FieldWorkDates OR the ID_Wells could possibly be smaller than the First record.

This could be accomplished in DAO code by running Listbox 1 first and apppending it to a blank recordset, then running the 2nd criteria and appending it.

Is there a way to accomplish this in a SQL Statement?

plog · Apr 7, 2016

You would make an calculated field in your ORDER BY clause to accomplish this. I don't understand your particulars, so let me use a simpler example. Suppose you had a list of names and you wanted 'Jane' to appear at the top of that query. You would use this query:

Code:

SELECT FirstName FROM NameTable ORDER BY Iif(FirstName="Jane", "AAA", FirstName)

Rx_ · Apr 7, 2016

Wow, so simple! thanks
Added a column at the end of the Select IIf(([APD_FieldWorkDates]=30121),0,1) AS TopSort
Then added at the end an Order By: ORDER BY IIf(([APD_FieldWorkDates]=30121),0,1);

Since the SQL Statement is dynamically created with VBA Code, it will be right every time!

Code:

The SQL code looks like:
SELECT APD_FieldWorkDate_2.APD_FieldWorkDates, APD_FieldWorkDate_2.ID_Wells, [Others not shown]
FROM APD_FieldWorkDate_2
WHERE (((APD_FieldWorkDate_2.APD_FieldWorkDates)=30121) AND ((APD_FieldWorkDate_2.ID_Wells)=8708) AND ((APD_FieldWorkDate_2.ID_Bio_Svy_Type)=15)) OR (((APD_FieldWorkDate_2.ID_Wells)=4996) AND ((APD_FieldWorkDate_2.ID_Bio_Svy_Type)=15))
ORDER BY IIf(([APD_FieldWorkDates]=30121),0,1);

Sort Order based on specific criteria when 3 criteria are provided (1 Viewer)

Rx_

Nothing In Moderation

Attachments

plog

Banishment Pending

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread