Order by ???

"select 1 as 'myOrder', * from [power] where description like '%" & strTest & "%'" & vbcrlf & _
"union" & vbcrlf & _
"select 2 as 'myOrder', * from [power] where description like '%" & strKeyword(0) & "%'" & vbcrlf & _
"order by myOrder, titel desc"

Jeg sætter det ind således:

strTest = (Request.Form("Keyword"))
strKeyword = split(Trim(Request.Form("Keyword")), " ")

       
        Select Case UBound(strKeyword)
       
        Case "0"
            strSQL =    "select 1 as 'myOrder', * from [power] where description like '%" & strTest & "%'" & vbcrlf & _
"union" & vbcrlf & _
"select 2 as 'myOrder', * from [power] where description like '%" & strKeyword(0) & "%'" & vbcrlf & _
"order by myOrder, titel desc"
        Case "1"
            strSQL = "select * from power where description like '%" & strTest & "%' OR '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%' " '2 ord i søgningen
        Case "2"
            strSQL = "select * from power where description like '%" & strTest & "%' OR '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%' and description like '%" & strKeyword(2) & "%' " '3 ord i søgningen
        Case Else
            strSQL = "select * from power where description like '%" & strTest & "%' OR '%" & strKeyword(0) & "%' and description like '%" & strKeyword(1) & "%' and description like '%" & strKeyword(2) & "%' " 'mere end 3 ord i søgningen
        End Select

Jeg kan ikke overskuehvor den wrappe , men den skal ihvertfald ende ud med:
-------
select 1 as 'myOrder', * from [power] where description like '%desc%'
union
select 2 as 'myOrder', * from [power] where description like '%keyw%'
order by myOrder, titel desc
-------

Jeg får fejl:

The ORDER BY expression (myOrder) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

sorry...
Jeg kan ikke overskuehvor den wrappe , men den skal ihvertfald ende ud med:
-------
select 1 as myOrder, * from [power] where description like '%desc%'
union
select 2 as myOrder, * from [power] where description like '%keyw%'
order by myOrder, titel desc
----------

Hmm, jeg forstår ikke lige hvad der sker, mne jeg prøver at rode lidt med det    :-)

Kan ikke få det til at virke :-(

Fejlen er:
Microsoft JET Database Engine error '80004005'

The ORDER BY expression (myOrder) includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.

der skal kun være EEn linje (efter alle unions) med:
order by myOrder, titel desc

Er ikke helt med.
Ser det her ikke fornuftigt ud ??

strSQL =  "select 1 as 'myOrder', * from [power] where description like '%strTest%'union select 2 as myOrder, * from [power] where description like '%strKeyword%' order by myOrder, titel desc"

Kan jeg ikke bare skrive det hele i EEn linie ??

strSQL =  "select 1 as myOrder, * from [power] where description like '%strTest%' union select 2 as myOrder, * from [power] where description like '%strKeyword%' order by myOrder, titel desc"

jo, skriv det bare i en linje. jeg synes bare det er lettere at læse på den anden måde :-)

Når jeg prøver med den her:

strSQL =  "select 1 as myOrder, * from [power]
where description like '%strTest%'
union
select 2 as myOrder, * from [power] where description like '%strKeyword(0)%' and description like '%" & strKeyword(1) & "%'
order by myOrder, titel desc"



Får jeg fejl, en ny dog:

Microsoft JET Database Engine error '80040e21'

Cannot use Memo, OLE, or Hyperlink Object field 'url' in the SELECT clause of a union query.

/tests/search.asp, line 131

Du kan gå en anden vej... tilret alle dine linier til:

"select (description like '%" & strTest & "%') AS testTrue, * from power...  --forskelligt indhold--  ...ORDER BY testTrue, titel DESC"

og lad resten være urørt!

skulle jo egentlig være et svar ;0)