Query Sort by Field Value

[Thom]

Need to sort a query based on a text field, and not in alphabetical order. In the datasheet for the table below, I need to return everything sorted by the Part_No. However not in alphabetical order by part number. First those which start with "L", then the others can be in alphabetical order - just need all the "L" part numbers first and ordered by Part_No. Here is some hypothetical sample data:



EQUIP

Part_No	Location	Version
LN-23yy	AL	A
FE-R72g	AL	G
LN-734r	AL	B
SC-003g	AL	C
VR-Z782	AL	A
FS-F655	NY	B
LN-447s	NY	C

This query will be used in a report, along with a subquery (which is straight forward with no problems creating). Was trying to do something with a LIKE statement in the WHERE clause, but that doesn't process correctly. Obviously cannot put ASC or DSC in a SORT BY clause for the PART_NO as the required order is not alphabetical. Please know that I am fairly new to MS Access, and honestly am not certain exactly how to do this. Sounds simple enough, but I've been working on it for many hours already.

Then thought maybe use a case statement, but this too does not work as it returns all the values with the WHERE clause (had to put the case in an ORDER BY clause to not error). Insights and help are very welcome!!

SELECT part_no, location, version
FROM equip
WHERE location = "AL"
ORDER BY CASE WHEN part_no LIKE "L*" THEN 1
WHEN part_no LIKE "F*" THEN 2
WHEN part_no LIKE "S*" THEN 3
WHEN part_no LIKE "V*" THEN 4 END ASC;

[CJ_London]

case when is sql server - is that what you are using?

The access equivalent is the switch function, but might be slow for a large dataset

If access I would instead try

SELECT part_no, location, version
 FROM equip
 WHERE location = "AL"
 ORDER BY iif(part_no LIKE "L*",0,1), Part_No

or just

SELECT part_no, location, version
 FROM equip
 WHERE location = "AL"
 ORDER BY part_no LIKE "L*", Part_No

[pbaldy]

If you are in SQL Server, I'd make a field out of the CASE statement and sort it in the form/report it's used for. That said, if in SQL Server you need to use single quotes and % as the wildcard:

CASE WHEN part_no LIKE 'L%' THEN 1

[Thom]

Thank you, using the iif statement works great! Like I said, I'm new to Access and did not know about this. My background is with Oracle and Oracle Application Express (APEX), so it's been an interesting learning curve.