Sort Date in a report

[scottian]

As the subject suggests Ive got a field in a table set to date and time, but i need to group on the field to return figures for each day for a report. The records sort fine in the query but when i view the report the records seem to sort as number i.e.-
19/03/2008
18/03/2008
18/02/2008
17/03/2008
17/02/2008

so it groups the same days of each of the months together as if they were numbers. Anyone have any ideas?



"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[maxhugen]

It looks like you are formatting the date - which returns a string - and sorting on that.

If that's the case, just add the field to the query a second time, unformatted, and sort on that.



Max Hugen
Australia

 

[Remou]

Use the sorting and grouping options of the report, choosing Day for Group On. There is a wizard that will do this.

 

[scottian]

Maxhugen,
Youre right i am formatting the date, but the query is fine with and sorts in the way that i wanted i also already tried the duplicate unformatted field in the query but no luck with that.
Remou,
Ive checked the sorting and grouping options and i cant find a wizard, this is what my grouping is set as :-
Assigned Date Descending
Group Header - Yes
Group Footer - Yes
Group On - Each Value
Group Interval - 1
Keep Together - Whole Group

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[maxhugen]

Is the "Assigned Date" that you're sorting on in the report the formatted date, or the unformatted date?

Max Hugen
Australia

 

[Remou]

As maxhugen says, do not format the date in the query. There is no point trying to sort a query to be used for a report, all sorting and grouping for reports should be done on the report.

The wizard I refer to is the reports wizard, not a sorting and grouping wizard.

 

[dhookom]

In addition to Remou's good advice on "no point trying to sort a query to be used for a report", I also find no point in formatting a field/column in a query since you can provide the formatting in the control in the report. Formatting fields in queries has a tendency to confuse.

Duane
Hook'D on Access
MS Access MVP

 

[scottian]

So i dont need to use group queries, it can be done on the report itself? is that right?

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[Remou]

Yes. Though it depends to some extend on what you want to do. Reports can group and you can hide or show detail, as required.

 

[scottian]

Ive tried the wizard, but when i finish i get 'No current record' then it doesnt save the report.

"Children are smarter than any of us. Know how I know that? I don't know one child with a full time job and children."...Bill Hicks

 

[dhookom]

Do you get the same error message when creating any report with the wizard? Can you create a report without the wizard. Most of us old-timers don't use the report wizards since it usually requires pwmm (post wizard major modifications).

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

Wizards are useful for seeing an example, I find.