Order By Date in Access SQL [Resolved]

[ober0330]

SELECT *
FROM [Cell Equipment Complete]
WHERE ((([Cell Equipment Complete].DATE_DUE) Between #1/1/2004# And #1/31/2004#))
ORDER BY [Cell Equipment Complete].DATE_DUE;

I'm trying to order entries by their date but Access doesn't want to do it on a report. I can do it if I just run the query, but when I go to use a report with the same query, it doesn't put them in date order.

[nkad]

This one i forget. But it may be something simple as one of the report/object properties?

[jhermiz]

Originally posted by ober5861

SELECT *
FROM [Cell Equipment Complete]
WHERE ((([Cell Equipment Complete].DATE_DUE) Between #1/1/2004# And #1/31/2004#))
ORDER BY [Cell Equipment Complete].DATE_DUE;

I'm trying to order entries by their date but Access doesn't want to do it on a report. I can do it if I just run the query, but when I go to use a report with the same query, it doesn't put them in date order.

Have you solved this?

[ober0330]

I tried changing the Order By options in the Report, but that doesn't seem to help. I've tried with it on and off.

[ober0330]

Originally posted by jhermiz
Have you solved this?

No, like I said, if I just double-click the query, the results come back ordered. But when I run the query through a report (after a user clicks on a button on a form), it doesn't work (the results are not ordered).

[nkad]

Should DESC or ASC be specified after the ORDER BY clause?

[ober0330]

I don't think so. It works without it when I just run the query.

[jhermiz]

Originally posted by ober5861
I don't think so. It works without it when I just run the query.

Right click on the report...goto sorting and grouping..look there.
If you have grouped by a field you cannot change that. That is why you can get a good query in SQL but not in the report.

Jon

[jhermiz]

Originally posted by nkad
Should DESC or ASC be specified after the ORDER BY clause?

No that is not the problem. By default an ORDER BY clause is returned as ASC.

Jon

[nkad]

Ok here is what i have done and it seems to work ok. I created a small report out of some old data i had. The report wizard set everything up for me so I didnt manually create it. But after it was created I went to View > Sorting and Grouping and there was my DateField. Check your grouping options too.

[ober0330]

Ahh... thanks Jon. It is sorting on other fields. I didn't create the report (I'm just making modifications) so I guess I'll just leave it the way they have it setup.

[jhermiz]

Originally posted by ober5861
Ahh... thanks Jon. It is sorting on other fields. I didn't create the report (I'm just making modifications) so I guess I'll just leave it the way they have it setup.

Anytime. In access, a query is second to a group / sort that is set on a report. It makes sense too, I've seen many people ask why o why...but it's rather straightforward...how else could you group by

You can still accomplish what you are trying to do by using a subreport.

Jon

[ober0330]

All I've been asked to do make the reports show different date periods. I've done that. I'll stop now until they ask for more