Sorting Dates

M

Melinda

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?
 
F

fredg

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.
 
A

akphidelt

If you have the dates properly formatted as a date then it should not be
doing that. The only thing I can see is that you have a text field and you
just type in 06-28-1986. The best thing to do is go in to the table where you
have the Seniority Date, change that field to Date/Time, and then format it
to Short Date. You might have to retype some of the dates though. Although
there might be a simpler solution.
 
M

Melinda

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 said:
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.
 
D

Duane Hookom

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 said:
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 said:
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.
 
M

Melinda

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 said:
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 said:
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 said:
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.
 
K

KARL DEWEY

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 said:
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 said:
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 said:
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.

:

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.
 
M

Melinda

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 said:
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 said:
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 said:
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


:

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.

:

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.
 
D

Duane Hookom

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 said:
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 said:
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 said:
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?

:

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


:

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.

:

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.
 
M

Melinda

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.
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 said:
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 said:
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


:

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?

:

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


:

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.

:

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.
 
K

KARL DEWEY

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 said:
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.
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 said:
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?

:

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


:

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?

:

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


:

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.

:

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.
 
M

Melinda

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 said:
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 said:
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.
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


:

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?

:

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


:

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?

:

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


:

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.

:

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.
 
D

Duane Hookom

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 said:
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 said:
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 said:
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


:

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?

:

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


:

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?

:

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


:

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.

:

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.
 
M

Melinda

=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 said:
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 said:
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 said:
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


:

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


:

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?

:

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


:

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?

:

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


:

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.

:

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.
 
K

KARL DEWEY

Why are you sorting on Outpost and Class No?
You said you were only concerned with Total Hours and SeniorityDate.
--
KARL DEWEY
Build a little - Test a little


Melinda said:
=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 said:
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 said:
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;


:

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


:

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


:

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?

:

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


:

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?

:

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


:

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.

:

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top