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...

 

[Micron]

Something else to consider - the field objMDatum might have the same name as a form control, in which case ambiguity is introduced when the Order by clause is added. The control reference Me.objID would suggest that since the control name doesn't seem to follow any naming convention. You could add a debug.print statement and copy/paste the sql from the immediate window into a new query. If it chokes, Access should highlight the offending portion in sql view. If not, switching to design view might show something obviously wrong. Aside from not using line continuation characters to begin with (which I'm not saying is the issue) I'd be more explicit about referencing fields in my sql statements, at least in the Order by part, such as

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

I agree that the semicolon is not required when constructing a sql statement in vba. Sorry if I messed up the continuation thing (as noted, I don't use them). Note that I also altered the Me reference. As for the suggestion to ORDER BY 2 DESC - I don't think you can sort on a single value, which is what that suggests to me.

 

[mole999]

Something else to consider - the field ORDER BY 2 DESC

In SQL it refers to output column 2, I haven't used Access for ages so don't know if that convention works

 

[Joe4]

As for the suggestion to ORDER BY 2 DESC - I don't think you can sort on a single value, which is what that suggests to me.

In SQL it refers to output column 2, I haven't used Access for ages so don't know if that convention works

It does work. It is not telling you to sort on a single value, it is telling you to sort on the second field that is returned in the SELECT clause, as mole99 suggests. I use this convention in both SQL and Access.

I don't think that is where the problem lies, though.

 

[Micron]

Thanks for the enlightenment; I didn't know that. Don't think I would ever use it beyond temporary testing since adding or removing fields in a query could easily mess up what it sorts on. I was always leery on that type of reference when referring to a recordset field by its ordinal position.

 

[Joe4]

It can come in handy if you have long field names, or if you are doing a UNION query where the fields you are returning don't all have the same name in each table/query.