Dates not sorting correctly in Report

JasonM · Apr 18, 2008

Good morning all! I am not sure if this is a Report problem or a Query
problem, but since I can get the data to display correctly in the Query some
of the time I think it is a query or data problem...without sorting inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it appears in
ascending date order. When I base a report on the Query, however, (even wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is calculated as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the same
results.

Thanks in advance for any assistance!

Jm

Duane Hookom · Apr 19, 2008

Do your calculated columns appear left or right aligned in your report's data
sheet? If left, then they are assumed to be text. You may need to wrap your
calculations in Val().

Don't waste time attempting to sort your report's record source query.
Sorting is best handled in the Sorting and Grouping dialog.

Jasonm · Apr 20, 2008

Duane, Thanks! I'll take a look at that first thing monday morning.

Jm

Duane Hookom said:
Do your calculated columns appear left or right aligned in your report's
data
sheet? If left, then they are assumed to be text. You may need to wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

JasonM said:

Good morning all! I am not sure if this is a Report problem or a Query
problem, but since I can get the data to display correctly in the Query
some
of the time I think it is a query or data problem...without sorting inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it appears
in
ascending date order. When I base a report on the Query, however, (even
wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is calculated as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report
problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

JasonM · Apr 21, 2008

Duane, Still no luck... The report is still seeing FiscalYear as text, but
when playing with the query a bit I can get it to show as numeric (right
justified), but it does not change the fact that the data from the date is
in the wrong order...
I have been experimenting with moving the sorting around (I.E. moving MrDate
up inthe grouping) but that tends to give me extra records in my headers and
footers. It does, however, put the dates in the correct order...
What can I post that will assist in the diagnosis of this problem? I am
running out of things to try... I have checked all of the dates that the
query is based on, and they are all valid dates. I will keep trying to alter
the grouping to get the results that I need, but I don't think that this is
the correct direction to correct the problem.

Thanks again for your efforts.

Jm

Duane Hookom said:
Do your calculated columns appear left or right aligned in your report's
data
sheet? If left, then they are assumed to be text. You may need to wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

JasonM said:

Good morning all! I am not sure if this is a Report problem or a Query
problem, but since I can get the data to display correctly in the Query
some
of the time I think it is a query or data problem...without sorting inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it appears
in
ascending date order. When I base a report on the Query, however, (even
wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is calculated as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report
problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

Duane Hookom · Apr 22, 2008

IMHO, the only true method of sorting a report is using the Sorting and
Grouping dialog. Forget the sorting in the report's record source query and
forget setting the Order By property.

Did you wrap any expression in Val() as suggested? What is the exact SQL of
your report's record source and what are the expressions you entered into
each of the sorting and grouping levels?

--
Duane Hookom
Microsoft Access MVP

JasonM said:
Duane, Still no luck... The report is still seeing FiscalYear as text, but
when playing with the query a bit I can get it to show as numeric (right
justified), but it does not change the fact that the data from the date is
in the wrong order...
I have been experimenting with moving the sorting around (I.E. moving MrDate
up inthe grouping) but that tends to give me extra records in my headers and
footers. It does, however, put the dates in the correct order...
What can I post that will assist in the diagnosis of this problem? I am
running out of things to try... I have checked all of the dates that the
query is based on, and they are all valid dates. I will keep trying to alter
the grouping to get the results that I need, but I don't think that this is
the correct direction to correct the problem.

Thanks again for your efforts.

Jm

Duane Hookom said:

Do your calculated columns appear left or right aligned in your report's
data
sheet? If left, then they are assumed to be text. You may need to wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

JasonM said:

Good morning all! I am not sure if this is a Report problem or a Query
problem, but since I can get the data to display correctly in the Query
some
of the time I think it is a query or data problem...without sorting inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it appears
in
ascending date order. When I base a report on the Query, however, (even
wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is calculated as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report
problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

Click to expand...

JasonM · Apr 22, 2008

Duane, thanks again for replying back. To answer your questions in the oder
they were asked:

I tried wrapping the field FiscalYear in Val() in both the report fields and
in the sorting with no change.
I slimmed doen the SQL (eliminated fields that I am currently not using in
the report) from the query and the SQL is as follows:
SELECT MonthlyReport.MRDate, Year([mrdate]) AS CalendarYear,
Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1) AS
FiscalYear, Val((Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1) AS
FiscalMonth, ([p1_running]+[p2_running]+[p3_running]+[p4_running]) AS
PumpsRunning, qryPumpsRunningBin.I1, qryPumpsRunningBin.I2,
qryPumpsRunningBin.I3, qryPumpsRunningBin.I4, MonthlyReport.INF,
MonthlyReport.EFF, MonthlyReport.BWFlow, MonthlyReport.MAX,
MonthlyReport.MIN, MonthlyReport.INCHES, MonthlyReport.LEVEL,
MonthlyReport.CFS, MonthlyReport.AVG, MonthlyReport.I1, MonthlyReport.I2,
MonthlyReport.I3, MonthlyReport.I4, MonthlyReport.E1, MonthlyReport.E2,
MonthlyReport.E3, MonthlyReport.E3A, MonthlyReport.E4, MonthlyReport.E5
FROM qryPumpsRunningBin INNER JOIN MonthlyReport ON
qryPumpsRunningBin.PumpDate = MonthlyReport.MRDate
WHERE
(((Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1))>=["What
fiscal Year?"]));
I have also tried deleting the inner joined query qryPumpsRunningBin, but it
does not change the outcome...
I am sorting/Grouping on the following fields:
FiscalYear - Ascending with a group Header and Footer
mrDate Ascending with a group Header Only (I have no data in the report
details section it is all summarized in the header or footer sections.).

What I find so bizzare is that only very few years exhibit this anomoly... I
have removed ALL sorting from the query. Again, thank-you for your interest.
I really appreciate any infor that you can offer.

Jm

Duane Hookom said:
IMHO, the only true method of sorting a report is using the Sorting and
Grouping dialog. Forget the sorting in the report's record source query
and
forget setting the Order By property.

Did you wrap any expression in Val() as suggested? What is the exact SQL
of
your report's record source and what are the expressions you entered into
each of the sorting and grouping levels?

--
Duane Hookom
Microsoft Access MVP

JasonM said:

Duane, Still no luck... The report is still seeing FiscalYear as text,
but
when playing with the query a bit I can get it to show as numeric (right
justified), but it does not change the fact that the data from the date
is
in the wrong order...
I have been experimenting with moving the sorting around (I.E. moving
MrDate
up inthe grouping) but that tends to give me extra records in my headers
and
footers. It does, however, put the dates in the correct order...
What can I post that will assist in the diagnosis of this problem? I am
running out of things to try... I have checked all of the dates that the
query is based on, and they are all valid dates. I will keep trying to
alter
the grouping to get the results that I need, but I don't think that this
is
the correct direction to correct the problem.

Thanks again for your efforts.

Jm

Duane Hookom said:

Do your calculated columns appear left or right aligned in your
report's
data
sheet? If left, then they are assumed to be text. You may need to wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

:

Good morning all! I am not sure if this is a Report problem or a Query
problem, but since I can get the data to display correctly in the
Query
some
of the time I think it is a query or data problem...without sorting
inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it
appears
in
ascending date order. When I base a report on the Query, however,
(even
wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is calculated
as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report
problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

Click to expand...

Duane Hookom · Apr 23, 2008

MonthlyReport sounds like the name of a query. If so, what is the SQL?
You can simplify your Fiscal Year like:
Year(DateAdd("m",6,[mrDate]))
and Fiscal Month
Month(DateAdd("m",6,[mrDate]))

--
Duane Hookom
Microsoft Access MVP

JasonM said:
Duane, thanks again for replying back. To answer your questions in the oder
they were asked:

I tried wrapping the field FiscalYear in Val() in both the report fields and
in the sorting with no change.
I slimmed doen the SQL (eliminated fields that I am currently not using in
the report) from the query and the SQL is as follows:
SELECT MonthlyReport.MRDate, Year([mrdate]) AS CalendarYear,
Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1) AS
FiscalYear, Val((Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1) AS
FiscalMonth, ([p1_running]+[p2_running]+[p3_running]+[p4_running]) AS
PumpsRunning, qryPumpsRunningBin.I1, qryPumpsRunningBin.I2,
qryPumpsRunningBin.I3, qryPumpsRunningBin.I4, MonthlyReport.INF,
MonthlyReport.EFF, MonthlyReport.BWFlow, MonthlyReport.MAX,
MonthlyReport.MIN, MonthlyReport.INCHES, MonthlyReport.LEVEL,
MonthlyReport.CFS, MonthlyReport.AVG, MonthlyReport.I1, MonthlyReport.I2,
MonthlyReport.I3, MonthlyReport.I4, MonthlyReport.E1, MonthlyReport.E2,
MonthlyReport.E3, MonthlyReport.E3A, MonthlyReport.E4, MonthlyReport.E5
FROM qryPumpsRunningBin INNER JOIN MonthlyReport ON
qryPumpsRunningBin.PumpDate = MonthlyReport.MRDate
WHERE
(((Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1))>=["What
fiscal Year?"]));
I have also tried deleting the inner joined query qryPumpsRunningBin, but it
does not change the outcome...
I am sorting/Grouping on the following fields:
FiscalYear - Ascending with a group Header and Footer
mrDate Ascending with a group Header Only (I have no data in the report
details section it is all summarized in the header or footer sections.).

What I find so bizzare is that only very few years exhibit this anomoly... I
have removed ALL sorting from the query. Again, thank-you for your interest.
I really appreciate any infor that you can offer.

Jm

Duane Hookom said:

IMHO, the only true method of sorting a report is using the Sorting and
Grouping dialog. Forget the sorting in the report's record source query
and
forget setting the Order By property.

Did you wrap any expression in Val() as suggested? What is the exact SQL
of
your report's record source and what are the expressions you entered into
each of the sorting and grouping levels?

--
Duane Hookom
Microsoft Access MVP

JasonM said:

Duane, Still no luck... The report is still seeing FiscalYear as text,
but
when playing with the query a bit I can get it to show as numeric (right
justified), but it does not change the fact that the data from the date
is
in the wrong order...
I have been experimenting with moving the sorting around (I.E. moving
MrDate
up inthe grouping) but that tends to give me extra records in my headers
and
footers. It does, however, put the dates in the correct order...
What can I post that will assist in the diagnosis of this problem? I am
running out of things to try... I have checked all of the dates that the
query is based on, and they are all valid dates. I will keep trying to
alter
the grouping to get the results that I need, but I don't think that this
is
the correct direction to correct the problem.

Thanks again for your efforts.

Jm

Do your calculated columns appear left or right aligned in your
report's
data
sheet? If left, then they are assumed to be text. You may need to wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

:

Good morning all! I am not sure if this is a Report problem or a Query
problem, but since I can get the data to display correctly in the
Query
some
of the time I think it is a query or data problem...without sorting
inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it
appears
in
ascending date order. When I base a report on the Query, however,
(even
wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is calculated
as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report
problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

Click to expand...

JasonM · Apr 23, 2008

Duane, MonthlyReport is the base table (I didn't name it, the db came to me
that way...) and thanks for the tip on the fiscal year and month! Those are
much simpler!

Jm

Duane Hookom said:
MonthlyReport sounds like the name of a query. If so, what is the SQL?
You can simplify your Fiscal Year like:
Year(DateAdd("m",6,[mrDate]))
and Fiscal Month
Month(DateAdd("m",6,[mrDate]))

--
Duane Hookom
Microsoft Access MVP

JasonM said:

Duane, thanks again for replying back. To answer your questions in the
oder
they were asked:

I tried wrapping the field FiscalYear in Val() in both the report fields
and
in the sorting with no change.
I slimmed doen the SQL (eliminated fields that I am currently not using
in
the report) from the query and the SQL is as follows:
SELECT MonthlyReport.MRDate, Year([mrdate]) AS CalendarYear,
Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1) AS
FiscalYear, Val((Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1) AS
FiscalMonth, ([p1_running]+[p2_running]+[p3_running]+[p4_running]) AS
PumpsRunning, qryPumpsRunningBin.I1, qryPumpsRunningBin.I2,
qryPumpsRunningBin.I3, qryPumpsRunningBin.I4, MonthlyReport.INF,
MonthlyReport.EFF, MonthlyReport.BWFlow, MonthlyReport.MAX,
MonthlyReport.MIN, MonthlyReport.INCHES, MonthlyReport.LEVEL,
MonthlyReport.CFS, MonthlyReport.AVG, MonthlyReport.I1, MonthlyReport.I2,
MonthlyReport.I3, MonthlyReport.I4, MonthlyReport.E1, MonthlyReport.E2,
MonthlyReport.E3, MonthlyReport.E3A, MonthlyReport.E4, MonthlyReport.E5
FROM qryPumpsRunningBin INNER JOIN MonthlyReport ON
qryPumpsRunningBin.PumpDate = MonthlyReport.MRDate
WHERE
(((Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1))>=["What
fiscal Year?"]));
I have also tried deleting the inner joined query qryPumpsRunningBin, but
it
does not change the outcome...
I am sorting/Grouping on the following fields:
FiscalYear - Ascending with a group Header and Footer
mrDate Ascending with a group Header Only (I have no data in the report
details section it is all summarized in the header or footer sections.).

What I find so bizzare is that only very few years exhibit this
anomoly... I
have removed ALL sorting from the query. Again, thank-you for your
interest.
I really appreciate any infor that you can offer.

Jm

Duane Hookom said:

IMHO, the only true method of sorting a report is using the Sorting and
Grouping dialog. Forget the sorting in the report's record source query
and
forget setting the Order By property.

Did you wrap any expression in Val() as suggested? What is the exact
SQL
of
your report's record source and what are the expressions you entered
into
each of the sorting and grouping levels?

--
Duane Hookom
Microsoft Access MVP

:

Duane, Still no luck... The report is still seeing FiscalYear as text,
but
when playing with the query a bit I can get it to show as numeric
(right
justified), but it does not change the fact that the data from the
date
is
in the wrong order...
I have been experimenting with moving the sorting around (I.E. moving
MrDate
up inthe grouping) but that tends to give me extra records in my
headers
and
footers. It does, however, put the dates in the correct order...
What can I post that will assist in the diagnosis of this problem? I
am
running out of things to try... I have checked all of the dates that
the
query is based on, and they are all valid dates. I will keep trying to
alter
the grouping to get the results that I need, but I don't think that
this
is
the correct direction to correct the problem.

Thanks again for your efforts.

Jm

Do your calculated columns appear left or right aligned in your
report's
data
sheet? If left, then they are assumed to be text. You may need to
wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source
query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

:

Good morning all! I am not sure if this is a Report problem or a
Query
problem, but since I can get the data to display correctly in the
Query
some
of the time I think it is a query or data problem...without sorting
inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it
appears
in
ascending date order. When I base a report on the Query, however,
(even
wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is
calculated
as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report
problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the
same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

Click to expand...

Duane Hookom · Apr 23, 2008

If you continue to have an issue, you could contact me off line and I would
be willing to trouble-shoot as time permits. You would need to send me a
private email to my first name @ my last name .Net.
--
Duane Hookom
Microsoft Access MVP

JasonM said:
Duane, MonthlyReport is the base table (I didn't name it, the db came to me
that way...) and thanks for the tip on the fiscal year and month! Those are
much simpler!

Jm

Duane Hookom said:

MonthlyReport sounds like the name of a query. If so, what is the SQL?
You can simplify your Fiscal Year like:
Year(DateAdd("m",6,[mrDate]))
and Fiscal Month
Month(DateAdd("m",6,[mrDate]))

--
Duane Hookom
Microsoft Access MVP

JasonM said:

Duane, thanks again for replying back. To answer your questions in the
oder
they were asked:

I tried wrapping the field FiscalYear in Val() in both the report fields
and
in the sorting with no change.
I slimmed doen the SQL (eliminated fields that I am currently not using
in
the report) from the query and the SQL is as follows:
SELECT MonthlyReport.MRDate, Year([mrdate]) AS CalendarYear,
Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1) AS
FiscalYear, Val((Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1) AS
FiscalMonth, ([p1_running]+[p2_running]+[p3_running]+[p4_running]) AS
PumpsRunning, qryPumpsRunningBin.I1, qryPumpsRunningBin.I2,
qryPumpsRunningBin.I3, qryPumpsRunningBin.I4, MonthlyReport.INF,
MonthlyReport.EFF, MonthlyReport.BWFlow, MonthlyReport.MAX,
MonthlyReport.MIN, MonthlyReport.INCHES, MonthlyReport.LEVEL,
MonthlyReport.CFS, MonthlyReport.AVG, MonthlyReport.I1, MonthlyReport.I2,
MonthlyReport.I3, MonthlyReport.I4, MonthlyReport.E1, MonthlyReport.E2,
MonthlyReport.E3, MonthlyReport.E3A, MonthlyReport.E4, MonthlyReport.E5
FROM qryPumpsRunningBin INNER JOIN MonthlyReport ON
qryPumpsRunningBin.PumpDate = MonthlyReport.MRDate
WHERE
(((Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1))>=["What
fiscal Year?"]));
I have also tried deleting the inner joined query qryPumpsRunningBin, but
it
does not change the outcome...
I am sorting/Grouping on the following fields:
FiscalYear - Ascending with a group Header and Footer
mrDate Ascending with a group Header Only (I have no data in the report
details section it is all summarized in the header or footer sections.).

What I find so bizzare is that only very few years exhibit this
anomoly... I
have removed ALL sorting from the query. Again, thank-you for your
interest.
I really appreciate any infor that you can offer.

Jm

IMHO, the only true method of sorting a report is using the Sorting and
Grouping dialog. Forget the sorting in the report's record source query
and
forget setting the Order By property.

Did you wrap any expression in Val() as suggested? What is the exact
SQL
of
your report's record source and what are the expressions you entered
into
each of the sorting and grouping levels?

--
Duane Hookom
Microsoft Access MVP

:

Duane, Still no luck... The report is still seeing FiscalYear as text,
but
when playing with the query a bit I can get it to show as numeric
(right
justified), but it does not change the fact that the data from the
date
is
in the wrong order...
I have been experimenting with moving the sorting around (I.E. moving
MrDate
up inthe grouping) but that tends to give me extra records in my
headers
and
footers. It does, however, put the dates in the correct order...
What can I post that will assist in the diagnosis of this problem? I
am
running out of things to try... I have checked all of the dates that
the
query is based on, and they are all valid dates. I will keep trying to
alter
the grouping to get the results that I need, but I don't think that
this
is
the correct direction to correct the problem.

Thanks again for your efforts.

Jm

Do your calculated columns appear left or right aligned in your
report's
data
sheet? If left, then they are assumed to be text. You may need to
wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source
query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

:

Good morning all! I am not sure if this is a Report problem or a
Query
problem, but since I can get the data to display correctly in the
Query
some
of the time I think it is a query or data problem...without sorting
inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it
appears
in
ascending date order. When I base a report on the Query, however,
(even
wit
hsorting and grouping) it still shows the dates in the wrong order!

I am grouping a report based upon our fiscal year which is
calculated
as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1

First question: Is this a query problem a data problem or a report
problem?
Second question: does anyone have any suggestions for a fix? I have
recreated both the query and report several times only to get the
same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

Click to expand...

JasonM · Apr 25, 2008

Duane, Thanks for the offer. I will beat on it a bit more and try not to
bother you with it. I am sure that I am missing something wuite simple.

Jason

Duane Hookom said:
If you continue to have an issue, you could contact me off line and I
would
be willing to trouble-shoot as time permits. You would need to send me a
private email to my first name @ my last name .Net.
--
Duane Hookom
Microsoft Access MVP

JasonM said:

Duane, MonthlyReport is the base table (I didn't name it, the db came to
me
that way...) and thanks for the tip on the fiscal year and month! Those
are
much simpler!

Jm

Duane Hookom said:

MonthlyReport sounds like the name of a query. If so, what is the SQL?
You can simplify your Fiscal Year like:
Year(DateAdd("m",6,[mrDate]))
and Fiscal Month
Month(DateAdd("m",6,[mrDate]))

--
Duane Hookom
Microsoft Access MVP

:

Duane, thanks again for replying back. To answer your questions in the
oder
they were asked:

I tried wrapping the field FiscalYear in Val() in both the report
fields
and
in the sorting with no change.
I slimmed doen the SQL (eliminated fields that I am currently not
using
in
the report) from the query and the SQL is as follows:
SELECT MonthlyReport.MRDate, Year([mrdate]) AS CalendarYear,
Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1)
AS
FiscalYear, Val((Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod 12+1)
AS
FiscalMonth, ([p1_running]+[p2_running]+[p3_running]+[p4_running]) AS
PumpsRunning, qryPumpsRunningBin.I1, qryPumpsRunningBin.I2,
qryPumpsRunningBin.I3, qryPumpsRunningBin.I4, MonthlyReport.INF,
MonthlyReport.EFF, MonthlyReport.BWFlow, MonthlyReport.MAX,
MonthlyReport.MIN, MonthlyReport.INCHES, MonthlyReport.LEVEL,
MonthlyReport.CFS, MonthlyReport.AVG, MonthlyReport.I1,
MonthlyReport.I2,
MonthlyReport.I3, MonthlyReport.I4, MonthlyReport.E1,
MonthlyReport.E2,
MonthlyReport.E3, MonthlyReport.E3A, MonthlyReport.E4,
MonthlyReport.E5
FROM qryPumpsRunningBin INNER JOIN MonthlyReport ON
qryPumpsRunningBin.PumpDate = MonthlyReport.MRDate
WHERE
(((Val(Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1))>=["What
fiscal Year?"]));
I have also tried deleting the inner joined query qryPumpsRunningBin,
but
it
does not change the outcome...
I am sorting/Grouping on the following fields:
FiscalYear - Ascending with a group Header and Footer
mrDate Ascending with a group Header Only (I have no data in the
report
details section it is all summarized in the header or footer
sections.).

What I find so bizzare is that only very few years exhibit this
anomoly... I
have removed ALL sorting from the query. Again, thank-you for your
interest.
I really appreciate any infor that you can offer.

Jm

IMHO, the only true method of sorting a report is using the Sorting
and
Grouping dialog. Forget the sorting in the report's record source
query
and
forget setting the Order By property.

Did you wrap any expression in Val() as suggested? What is the exact
SQL
of
your report's record source and what are the expressions you entered
into
each of the sorting and grouping levels?

--
Duane Hookom
Microsoft Access MVP

:

Duane, Still no luck... The report is still seeing FiscalYear as
text,
but
when playing with the query a bit I can get it to show as numeric
(right
justified), but it does not change the fact that the data from the
date
is
in the wrong order...
I have been experimenting with moving the sorting around (I.E.
moving
MrDate
up inthe grouping) but that tends to give me extra records in my
headers
and
footers. It does, however, put the dates in the correct order...
What can I post that will assist in the diagnosis of this problem?
I
am
running out of things to try... I have checked all of the dates
that
the
query is based on, and they are all valid dates. I will keep trying
to
alter
the grouping to get the results that I need, but I don't think that
this
is
the correct direction to correct the problem.

Thanks again for your efforts.

Jm

Do your calculated columns appear left or right aligned in your
report's
data
sheet? If left, then they are assumed to be text. You may need to
wrap
your
calculations in Val().

Don't waste time attempting to sort your report's record source
query.
Sorting is best handled in the Sorting and Grouping dialog.
--
Duane Hookom
Microsoft Access MVP

:

Good morning all! I am not sure if this is a Report problem or a
Query
problem, but since I can get the data to display correctly in
the
Query
some
of the time I think it is a query or data problem...without
sorting
inteh
query I get data anomolies as shown below:

qryAnnualFiscalTest MRDate Year FiscalYear FiscalMonth
5/25/2005 2005 2005 11
5/26/2005 2005 2005 11
5/27/2005 2005 2005 11
9/20/2005 2005 2006 3
9/21/2005 2005 2006 3
9/22/2005 2005 2006 3
9/23/2005 2005 2006 3
9/24/2005 2005 2006 3
9/25/2005 2005 2006 3
9/26/2005 2005 2006 3
9/27/2005 2005 2006 3
9/28/2005 2005 2006 3
9/29/2005 2005 2006 3
9/30/2005 2005 2006 3
10/1/2005 2005 2006 4
10/2/2005 2005 2006 4
10/3/2005 2005 2006 4
10/4/2005 2005 2006 4
10/5/2005 2005 2006 4
5/28/2005 2005 2005 11
5/29/2005 2005 2005 11
5/30/2005 2005 2005 11
5/31/2005 2005 2005 11
6/1/2005 2005 2005 12
6/2/2005 2005 2005 12

The data in the table is sequential; I can sort the query and it
appears
in
ascending date order. When I base a report on the Query,
however,
(even
wit
hsorting and grouping) it still shows the dates in the wrong
order!

I am grouping a report based upon our fiscal year which is
calculated
as
follows:
FiscalYear:
Year([mrdate])-IIf([mrdate]<DateSerial(Year([mrdate]),7,1),1,0)+1

I also have created a field for fiscal month:
FiscalMonth: (Month([mrdate])+IIf(Day([mrdate])<1,7,6)-1) Mod
12+1

First question: Is this a query problem a data problem or a
report
problem?
Second question: does anyone have any suggestions for a fix? I
have
recreated both the query and report several times only to get
the
same
results.

Thanks in advance for any assistance!

Jm

Click to expand...

Click to expand...

Dates not sorting correctly in Report

JasonM

Duane Hookom

Jasonm

JasonM

Duane Hookom

JasonM

Duane Hookom

JasonM

Duane Hookom

JasonM