Re: Sorting Dates

=Iif([Outpost],2,1), Ascending with a header and a footer (first sort)
Class No, Asc, with a header and footer
Total hours, Asc with a header
SeniorityDate,Asc with a header
Thanks for all the time you have looked at this, but I still can not get the
dates to sort properly.

"Duane Hookom" wrote:

Sorting in your query is practically worthless. Please provide the sorting
and grouping expressions used in your report design view. This is the only
place that I trust any sorting in a report.

--
Duane Hookom
Microsoft Access MVP


"Melinda" wrote:

I believe I have the viewing and sorting correct, but I thought I would post
my SQL and maybe you would see something wrong in that.

SELECT [Total Query].Outpost, [Total Query].ClassNo, [Total
Query].EmployeeName, tblEmployees.SeniorityDate, tblEmployees.HomePhone,
tblEmployees.CellPhone, tblEmployees.Pagers, odotqry.[Total Hours]
FROM ([Total Query] INNER JOIN tblEmployees ON [Total Query].Tmsemployeeno =
tblEmployees.Tmsemployeeno) INNER JOIN odotqry ON tblEmployees.Tmsemployeeno
= odotqry.Tmsemployeeno
ORDER BY tblEmployees.SeniorityDate;


"KARL DEWEY" wrote:

Open your report in design view, click on menu VIEW - Sorting And Grouping.
Select the overtime hours totals field and Descinding order That is if you
want the one with the most overtime). Then below that select the 'seniority
date' field.

--
KARL DEWEY
Build a little - Test a little


"Melinda" wrote:

It is in the query, I misspoke. Just can't figure how to make it sort with
the most senior (seniority date) first when they have the same total year to
date hours.

Duane Hookom" wrote:

Regarding the seniority date, you stated "because it is not in the query".
Your report can't sort on a field that isn't displayed in the report's field
list.
--
Duane Hookom
Microsoft Access MVP


"Melinda" wrote:

OK, I went back into the tblEmployees field and typed in 4/21 and I get an
error, it does not change the field to the date of 4/21/2008. I took out
the input mask and now I can type 4-21 and it populates the rest of the
field. I went back and looked at my report and it still has the 3 employees
who have a total of 16.4 ytd overtime hours sorted incorrectly. First
seniority date 05-30-2000, second seniority date 06-18-1990 and third
seniority date of 06-23-1986. It should be reporting the dates the oposite.
Should I possibly put in the "Orderby" of the report,
(Format[Senioritydate[, "yyyy") Would something like that work?

"KARL DEWEY" wrote:

A fast simple test of your date field is to type 4/21 and press ENTER.
If the Access field is DateTime datatype it will change the 4/21 to
4/21/2008. If it is a text field it will remain 4/21.
--
KARL DEWEY
Build a little - Test a little


"Melinda" wrote:

Sorry, but I am a bit confused. Back in the table I have the Seniority
Date as a Date/Time field, and the format at the bottom is Short Date. When
I wrote the report I have the seniority date coming from tblEmployees, is
that possibly where the problem is because it is not in the query? I have
checked and rechecked everything you guys have said, but if I am looking at
it correct it appears that I have everything ok. Anything else I can check?

"Duane Hookom" wrote:

Fredg is correct as usual. Your statement "I also have it set up as a short
date" raises a red flag. You don't normally provide a format to a numeric or
date value you want sort on. Leave it alone.

Also, are you sure the Total Hours doesn't include more decimal places that
aren't displayed?
--
Duane Hookom
Microsoft Access MVP


"Melinda" wrote:

I do have it as a "Date Datatye". I am sorting in the report, I am first
sorting by a ClassNo, 2nd, Total Hours, and 3rd SeniorityDate. I have the
SeniorityDate set up as Date field in the table and that is where I am
bringing into the report from. I didn't know if I could go into the record
souce from the actual report and type descending or something of that sort.
I also have it set up as a short date.

"fredg" wrote:

On Fri, 18 Apr 2008 10:25:00 -0700, Melinda wrote:

I have looked through all the postings, but don't see one that quite fits
what I am doing, and have failed at all the attempts I have made. I am
sorting Seniority Dates in a total overtime report. When 3 employees have
the same year to date total hours of overtime, I need it to then sort by
seniority, with the most senior person being called in to work first. I
have three employees with 16.4 hours of overtime, and their seniority dates
are 05-13-2000, 06-14-1990, and 06-28-1986. I see that it is sorting on the
month and then the date, but I need the most senior person being listed
first. How do I change my sort?

What is the actual datatype of the Seniority Date field?
Is it Text datatype or is it Date datatype.?
Where are you doing the sorting? In the query or in the Report?

1) The Seniority Date field should be Date datatype (I suspect yours
is Text).
2) Any sorting you do in the query is irrelevant to the sorting in the
report.
To sort the report, use the Report's Sorting and Grouping dialog...
Click on View + Sorting and Grouping.

If the [SeniorityDate] is actually a Date datatype .....
In the Field/Expression column write:
[SeniorityDate]
on the top line.
In the Sort column write
Ascending


If the [SeniorityDate] is actually a Text datatype .....
In the Field/Expression column write:
=CDate([SeniorityDate])
on the top line
In the Sort Column, write
Ascending

The report should now sort according to Seniority.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

.

Relevant Pages

  • RE: Multi pages
    ... That is why I add the sort. ... When I add the sort and place the sum function ... THe source is just the query which does not have totals. ... totals of a few fields on the report, which it does fine with the sort, but I ...
    (microsoft.public.access.reports)
  • RE: Sorting Dates
    ... If this doesn't sort it out, I would re-create the report in a very ... "Duane Hookom" wrote: ... What is the SQL view of the record source of the report? ... Seniority Date/Hire Date seems to be where the trouble is. ...
    (microsoft.public.access.reports)
  • Re: Sorting in a Report
    ... Open the report in design view. ... Set it to the name of the query you just saved. ... sort them by the total score of each team. ... or you might be able to design another Totals query as an input ...
    (microsoft.public.access.reports)
  • Re: Sorting Dates
    ... Open your report in design view, click on menu VIEW - Sorting And Grouping. ... Select the overtime hours totals field and Descinding order That is if you ... Then below that select the 'seniority ...
    (microsoft.public.access.reports)
  • RE: Sorting Dates
    ... "Duane Hookom" wrote: ... it is text and will not sort properly in the report. ... Sort on the date field value, ... Seniority Date/Hire Date seems to be where the trouble is. ...
    (microsoft.public.access.reports)