Ordering not correct in report

[Daz84]

Sorry if this is the wrong forum, it is relating to SQL in the context of an access database, there didn't seem to be a relevant forum for access specifically.

 

My issue is this, I have the following query which runs exactly as expected when I run it by itself

SELECT jobs.reference, workshops.name AS workshops_name, jobs.receiptno, jobs.name AS jobs_name, jobs.estimate, workshops.phone, workshops.address, jobs.item, jobs.date
FROM workshops INNER JOIN jobs ON workshops.name=jobs.workshop
WHERE (((workshops.name)=Forms!CreateReport!Workshop) And ((jobs.date)>=Forms!CreateReport!DateGoFrom And (jobs.date)<=Forms!CreateReport!DateGoTo))
ORDER BY jobs.date;

 

However when I use this query as the control source for a report the output of the report is always ordered by DESC. I have tried explicitly declaring ORDER BY jobs.date ASC but it seems to have no effect 

 

As you will see from the WHERE clause I am using a control form called 'CreateReport' to handle the inputs but running the query directly and simply typing the inputs in has yeilded exactly the same output in all of my tests so it is not the form that is the issue. Also since the query seems to run fine when I run it independantly I suspect it is not an issue with the SQL either but in the actual report, so I am stuck. The report should not be choosing how to order it's input

 

Is anyone able to clarify exactly what is going on and how I can rectify the issue? If you need any more information please ask and I shall supply.

 

Thanks

Daz

[fenway]

Then jobs.date isn't a DATE field.

[Daz84]

jobs.date definately is a date field. But even if it wasn't surely I would still be able to order it properly?

 

The report is ordered by jobs.date in descending order, if it was not a date field it would not manage that properly. All I want to do is reverse the order exactly

[ajlisowski]

is there an index on jobs.date?

 

I just discovered that if you order by an index DESC will not reverse. If this is the case, try to ORDER BY jobs.date, 1