Order by sql

[silentwolf]

Hi guys,

can't seam to get this right.

Here is my code and it works fine... however I would like to ORDER objMDatum in DESC order..
Unfortunatelly I have not been able to get it right .. Can someone help with this please?

Private Sub Form_Current()
    Dim strSQL As String
    
    strSQL = "SELECT objMID, objMDatum, objMAnfang, objMEnde, objMPause, objMObjIDRef " & _
    "FROM tblObjMitarbeiter WHERE objMObjIDRef= " & Me!objID
   
    Me!lstObjektStunden.RowSource = strSQL

End Sub

oh Sorry this is in the wrong forum! It s Access...

 

[Joe4]

Try:

    strSQL = "SELECT objMID, objMDatum, objMAnfang, objMEnde, objMPause, objMObjIDRef " & _
    "FROM tblObjMitarbeiter WHERE objMObjIDRef= " & Me!objID & _
    " ORDER BY [COLOR=#574123]objMDatum DESC";[/COLOR]

 

[silentwolf]

Hi Joe4,
thanks for your reply!
Unfortunatelly this is not working

    strSQL = "SELECT objMID, objMDatum, objMAnfang, objMEnde, objMPause, objMObjIDRef " & _
    "FROM tblObjMitarbeiter WHERE objMObjIDRef= " & Me!objID & _
    " ORDER BY [COLOR=#574123]objMDatum DESC"[/COLOR]

The last semicolumn gives a syntax error but when I leave it out like above no records are displayed at all..

 

[mole999]

the ORDER BY won't stop you getting records

the syntax is

SELECT
FROM
WHERE
ORDER BY

test the SELECT FROM then build from there

 

[silentwolf]

Hi mole999,
sorry I don't really understand what you mean.
Without to ORDER BY it gives me the right filter back but as soon I put the ORDER BY into the statement no records are displayed

 

[mole999]

how about

ORDER BY 2 DESC

 

[james_lankford]

Hi Joe4,
thanks for your reply!
Unfortunatelly this is not working

    strSQL = "SELECT objMID, objMDatum, objMAnfang, objMEnde, objMPause, objMObjIDRef " & _
    "FROM tblObjMitarbeiter WHERE objMObjIDRef= " & Me!objID & _
    " ORDER BY [COLOR=#574123]objMDatum DESC"[/COLOR]

The last semicolumn gives a syntax error but when I leave it out like above no records are displayed at all..

there is NO semicolon in that code

 

[Joe4]

Sorry, in my original reply I had:

    " ORDER BY [COLOR=#574123]objMDatum DESC";[/COLOR]

The semi-colon usually goes inside the quotes, i.e.

    " ORDER BY [COLOR=#574123]objMDatum DESC;"[/COLOR]

but it usually isn't necessary at all (you can usually just take it or leave it).

But an "Order By" does not do anything to filter records. If it is showing no data, then it is probably your part of the code that isn't working right.

 

[tusharm]

What is the value in ObjID? Is that somehow messing up the SQL statement when you have an ORDER BY clause after the WHERE?

Hi mole999,
sorry I don't really understand what you mean.
Without to ORDER BY it gives me the right filter back but as soon I put the ORDER BY into the statement no records are displayed

 

[silentwolf]

Hi guys,
thanks for replying! Unfortunatelly I can not check it today but will keep you posted on how I am going!