Sortering af tekst-felt, som numerisk felt

Mit bedste bud er at lave et sorteringsfelt mere, som opdateres ved indsættelse og ændring. Indsætter du "2biler" i det rigtige felt, indsætter du tallet 2 i sorteringsfeltet. "100knallerter" giver 100 i feltet, mens "elefantmad" giver 99999999 (et eller andet maksimum du har styr på)

Så kan du

  SELECT .... ORDER BY sorteringsfelt ASC, ditnormalefelt ASC

Ja, ok...det var også den løsning jeg selv havde overvejet, men ville gerne undgå at ændre i backend, hvis det kunne lade sig gøre...

Du vil ikke kunne få en effektiv sortering, hvis Access skal ind og manipulere det felt, der sorteres på ved hver sortering. Jeg tror det foreslåede er den eneste løsning, men vent og se om der kommer andre forslag.

Mit bud er også, at det ikke kan lade sig gøre, hvis de felter du vil have sorteret nummerisk også indeholder tekst.

Hvis de ikke indeholder tekst, vil du i hvert fald kunne klare det i to omgange. Først tages de nummeriske:

SELECT id,felt FROM tabel1 WHERE isNumeric(felt) = '-1' ORDER BY Round(felt);

Derefter tages de ikke-nummeriske

SELECT id,felt FROM tabel1 WHERE isNumeric(felt) = '0' ORDER BY felt;

[disse udtag vil sikkert kunne sættes sammen med UNION, har bare ikke fundet en måde, som ikke laver rod i ORDER]..

Jeg ved ikke lige umiddelbart hvordan man skulle få Access til at "isolere" et nummer i et tekstfelt, hvis der også står tekst i tekstfeltet. Hvad nu hvis der i et felt står '12b113' - skal det så være 12 eller 113 som er nummeret?

Denne SQL-sætning outputter alle strenge der indeholder tal:
* De strenge som kun indeholder ét sammenhængende tal bliver sat korrekt i orden.
* Dem med flere tal - f.eks. '12b13' kommer med, men efter alt det andet.
* Strenge uden tal kommer IKKE med.

Tabelnavn: tabel1
Felter: id (primary key,auto etc.),f1 (text)

---

SELECT * FROM (SELECT DISTINCT id,l1, Min(start) AS fstart, Max(slut) AS fslut, right(left(l1,fslut),len(l1) - fstart +1) as ftal
FROM (SELECT * FROM
(SELECT id,l1,'0' as tal,Instr(l1,0) as start,InstrRev(l1,0) as slut FROM tabel1) UNION
(SELECT id,l1,'1' as tal,Instr(l1,1) as start,InstrRev(l1,1) as slut FROM tabel1) UNION
(SELECT id,l1,'2' as tal,Instr(l1,2) as start,InstrRev(l1,2) as slut FROM tabel1) UNION
(SELECT id,l1,'3' as tal,Instr(l1,3) as start,InstrRev(l1,3) as slut FROM tabel1) UNION
(SELECT id,l1,'4' as tal,Instr(l1,4) as start,InstrRev(l1,4) as slut FROM tabel1) UNION
(SELECT id,l1,'5' as tal,Instr(l1,5) as start,InstrRev(l1,5) as slut FROM tabel1) UNION
(SELECT id,l1,'6' as tal,Instr(l1,6) as start,InstrRev(l1,6) as slut FROM tabel1) UNION
(SELECT id,l1,'7' as tal,Instr(l1,7) as start,InstrRev(l1,7) as slut FROM tabel1) UNION
(SELECT id,l1,'8' as tal,Instr(l1,8) as start,InstrRev(l1,8) as slut FROM tabel1) UNION
(SELECT id,l1,'9' as tal,Instr(l1,9) as start,InstrRev(l1,9) as slut FROM tabel1))
WHERE start Not Like '0'
GROUP BY id, l1) ORDER BY Round(ftal);

Jeg lavede en lille tastefejl:
String-feltet hedder 'l1' og ikke 'f1'.

En yderligere variant:

---

SELECT * FROM (SELECT DISTINCT id,l1, Min(start) AS fstart, Max(slut) AS fslut, right(left(l1,fslut),len(l1) - fstart +1) as ftal
FROM (SELECT * FROM
(SELECT id,l1,'0' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'1' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'2' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'3' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'4' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'5' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'6' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'7' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'8' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1) UNION
(SELECT id,l1,'9' as tal,Instr(l1,tal) as start,InstrRev(l1,tal) as slut FROM tabel1))
WHERE start Not Like '0'
GROUP BY id, l1) ORDER BY Round(ftal);

--
^ Her bruges feltet 'tal' reelt til noget - der undgås gentagelser.. 

Ellers kan denne være lige så god (og samtidig mere kompakt):

---

SELECT * FROM (SELECT DISTINCT id,l1, Min(start) AS fstart, Max(slut) AS fslut, right(left(l1,fslut),len(l1) - fstart +1) as ftal
FROM (SELECT * FROM
(SELECT id,l1,Instr(l1,0) as start,InstrRev(l1,0) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,1) as start,InstrRev(l1,1) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,2) as start,InstrRev(l1,2) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,3) as start,InstrRev(l1,3) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,4) as start,InstrRev(l1,4) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,5) as start,InstrRev(l1,5) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,6) as start,InstrRev(l1,6) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,7) as start,InstrRev(l1,7) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,8) as start,InstrRev(l1,8) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,9) as start,InstrRev(l1,9) as slut FROM tabel1))
WHERE start Not Like '0'
GROUP BY id, l1) ORDER BY Round(ftal);

--

Tredje gang er lykkens gang. En lille bug: ftal skal selvfølgelig defineres sådan her:

right(left(l1,fslut),fslut - fstart + 1) as ftal

--

SELECT * FROM (SELECT DISTINCT id,l1, Min(start) AS fstart, Max(slut) AS fslut, right(left(l1,fslut),fslut - fstart + 1) as ftal
FROM (SELECT * FROM
(SELECT id,l1,Instr(l1,0) as start,InstrRev(l1,0) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,1) as start,InstrRev(l1,1) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,2) as start,InstrRev(l1,2) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,3) as start,InstrRev(l1,3) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,4) as start,InstrRev(l1,4) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,5) as start,InstrRev(l1,5) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,6) as start,InstrRev(l1,6) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,7) as start,InstrRev(l1,7) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,8) as start,InstrRev(l1,8) as slut FROM tabel1) UNION
(SELECT id,l1,Instr(l1,9) as start,InstrRev(l1,9) as slut FROM tabel1))
WHERE start Not Like '0'
GROUP BY id, l1) ORDER BY Round(ftal);

Smider du et svar?

ok. jeg lukker... hvis du ønsker point, så smid blot en kommentar her, så finder vi ud af det :-)