RhysDavies


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.




Re: Order by in select statement not working

ADG


Hi

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






Re: Order by in select statement not working

Andy Pope

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





Re: Order by in select statement not working

RhysDavies

Hi - thanks for the reply both but i have tried that too.

i get

compile error:

Expected: end of statement





Re: Order by in select statement not working

Andy Pope

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"





Re: Order by in select statement not working

RhysDavies

Thanks Andy, that sorted it.