Order By day of week using Format(date, "w")

[ejm8]

Hello,

I'm using an SQL string to populate a multiselect listbox with dates. It's something like this:

select job_date, format(job_date, "ddd") as Day from job_list;

...and I want to sort by Day of Week in order (i.e. Mon, Tue, Wed, Thu, Fri, Sat). So I tried this:

select job_date, format(job_date, "ddd") as Day from job_list order by format(job_date, "w");

...and I don't get any results. Any suggestions?

Thank you

 

[ejm8]

I left out that I'm using "select DISTINCT".

When I copy the SQL string into a query, I get an error message saying "ORDER BY clause (format(job_date,"w")) conflicts with DISTINCT".

However, if I change it to "order by format(job_date,"ddd")" I don't get the error, BUT I also don't get the sort order I want.

 

[Remou]

Maybe:
[tt]SELECT DISTINCT job_date, Format(job_date,"ddd") AS [Day], Format(job_date,"w")
FROM statistics
ORDER BY Format(job_date,"w");[/tt]

 

[ejm8]

That does it. Thank you Remou!

I just had to include the format(job_date,"w") in the select part as well.