locked
Order by in select statement not working RRS feed

  • Question

  • Hi there - i have a select statement that runs on form load that allows selection of appropriate company names from a combo box based on if the user is the owner of a company, which works great, syntax is below;

    Me.companyname.RowSource = "SELECT [companyname] FROM [qryactivity] WHERE [active] = Yes AND [leadofficer] ='" & loginname & "'"

    However i want to sort them alphabetically by name but cannot get it to work, i keep getting syntax errors, i have added the following code but it does not like it:

    Me.companyname.RowSource = "SELECT [companyname] FROM [qryactivity] ORDER BY [companyname]ASC WHERE [active] = Yes AND [leadofficer] ='" & loginname & "'"

    any help would be greatly appreciated.

    Rhys.

    Monday, December 18, 2006 8:38 AM

Answers

  • Sorry, should have checked the syntax properly. You need to make the order by part of the string being built.

    Me.companyname.RowSource = "SELECT [companyname] FROM [qryactivity] 
    WHERE [active] = Yes AND [leadofficer] ='" & loginname & "'
    ORDER BY [companyname]ASC"
    Monday, December 18, 2006 3:02 PM

All replies

  • Hi

    Try moving the Order By to the end after the Where statement

    Monday, December 18, 2006 11:02 AM
  • I think it's just the ordering of your statement is incorrect.

    Me.companyname.RowSource = "SELECT [companyname] FROM [qryactivity]  WHERE [active] = Yes AND [leadofficer] ='" & loginname & "'"
    ORDER BY [companyname]ASC
    Monday, December 18, 2006 11:16 AM
  • Hi - thanks for the reply both but i have tried that too.

    i get

    compile error:

    Expected: end of statement

    Monday, December 18, 2006 11:57 AM
  • Sorry, should have checked the syntax properly. You need to make the order by part of the string being built.

    Me.companyname.RowSource = "SELECT [companyname] FROM [qryactivity] 
    WHERE [active] = Yes AND [leadofficer] ='" & loginname & "'
    ORDER BY [companyname]ASC"
    Monday, December 18, 2006 3:02 PM
  •  

    Thanks Andy, that sorted it.

    Monday, December 18, 2006 3:23 PM