Trying to get my report to sort correctly

[fcmedina78]

I have a report based on a query that should sort assending. I used the
DatePart function to display dates as weeks and the query sees the dates as a
number but my report see's it has text and will not wort correctly. I tried
wrapping the extression in the Val() function with no success. Here is the
query I used:

SELECT tblWinEntry.[Store Number], DatePart("yyyy",[DateEntry]) AS [Year],
DatePart("q",[DateEntry]) AS Quarter, Val(DatePart("ww",[DateEntry])) AS
Week, Sum(tblWinEntry.[Win Amount]) AS [SumOfWin Amount]
FROM tblWinEntry
GROUP BY tblWinEntry.[Store Number], DatePart("yyyy",[DateEntry]),
DatePart("q",[DateEntry]), Val(DatePart("ww",[DateEntry]))
HAVING
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmReportSelection]![QuarterlyYearSelector])
AND
((DatePart("q",[DateEntry]))=[Forms]![frmReportSelection]![QuarterSelection]))
ORDER BY DatePart("q",[DateEntry]), Val(DatePart("ww",[DateEntry]));

Any Help???
Thanks in advance

 

[John Spencer]

I notice that your query has an order by clause. Do you know that reports
IGNORE the order by clause of the query?

You need to set up the order and grouping (if any) of a report by using the
report's Sorting and Grouping dialog. From the menu, View: Sorting and Grouping.

You can then set up to ten(?) levels of sorting or grouping for you report.

If this is not the problem, I apologize for making the assumption that you did
not know how to set the sorting of a report.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[fcmedina78]

Hi John, thanks for your response.

On my report I am currently using the order and grouping options. I also
realize that the sorting and grouping in my query have no bearing in my
report. My assumption is that the source of my problem is rooted in the fact
that for some reason my query views my weeks (dates) as a numerical value and
sorts accordingly but my report see's the same value as text and appears to
be sorting the data as text and not the numerical value that it is. I'm not
sure if that was very clear but I think it is whats causing my problem.

Thanks for your help, and if you have any other idea's they are greatly
appreciated.

I notice that your query has an order by clause. Do you know that reports
IGNORE the order by clause of the query?

You need to set up the order and grouping (if any) of a report by using the
report's Sorting and Grouping dialog. From the menu, View: Sorting and Grouping.

You can then set up to ten(?) levels of sorting or grouping for you report.

If this is not the problem, I apologize for making the assumption that you did
not know how to set the sorting of a report.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have a report based on a query that should sort assending. I used the
DatePart function to display dates as weeks and the query sees the dates as a
number but my report see's it has text and will not wort correctly. I tried
wrapping the extression in the Val() function with no success. Here is the
query I used:

SELECT tblWinEntry.[Store Number], DatePart("yyyy",[DateEntry]) AS [Year],
DatePart("q",[DateEntry]) AS Quarter, Val(DatePart("ww",[DateEntry])) AS
Week, Sum(tblWinEntry.[Win Amount]) AS [SumOfWin Amount]
FROM tblWinEntry
GROUP BY tblWinEntry.[Store Number], DatePart("yyyy",[DateEntry]),
DatePart("q",[DateEntry]), Val(DatePart("ww",[DateEntry]))
HAVING
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmReportSelection]![QuarterlyYearSelector])
AND
((DatePart("q",[DateEntry]))=[Forms]![frmReportSelection]![QuarterSelection]))
ORDER BY DatePart("q",[DateEntry]), Val(DatePart("ww",[DateEntry]));

Any Help???
Thanks in advance

 

[fcmedina78]

Problem solved the sort in my query was throwing off the sort in my report
(that shouldn't happen but oh well, it works now)

Hi John, thanks for your response.

On my report I am currently using the order and grouping options. I also
realize that the sorting and grouping in my query have no bearing in my
report. My assumption is that the source of my problem is rooted in the fact
that for some reason my query views my weeks (dates) as a numerical value and
sorts accordingly but my report see's the same value as text and appears to
be sorting the data as text and not the numerical value that it is. I'm not
sure if that was very clear but I think it is whats causing my problem.

Thanks for your help, and if you have any other idea's they are greatly
appreciated.

I notice that your query has an order by clause. Do you know that reports
IGNORE the order by clause of the query?

You need to set up the order and grouping (if any) of a report by using the
report's Sorting and Grouping dialog. From the menu, View: Sorting and Grouping.

You can then set up to ten(?) levels of sorting or grouping for you report.

If this is not the problem, I apologize for making the assumption that you did
not know how to set the sorting of a report.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have a report based on a query that should sort assending. I used the
DatePart function to display dates as weeks and the query sees the dates as a
number but my report see's it has text and will not wort correctly. I tried
wrapping the extression in the Val() function with no success. Here is the
query I used:

SELECT tblWinEntry.[Store Number], DatePart("yyyy",[DateEntry]) AS [Year],
DatePart("q",[DateEntry]) AS Quarter, Val(DatePart("ww",[DateEntry])) AS
Week, Sum(tblWinEntry.[Win Amount]) AS [SumOfWin Amount]
FROM tblWinEntry
GROUP BY tblWinEntry.[Store Number], DatePart("yyyy",[DateEntry]),
DatePart("q",[DateEntry]), Val(DatePart("ww",[DateEntry]))
HAVING
(((DatePart("yyyy",[DateEntry]))=[Forms]![frmReportSelection]![QuarterlyYearSelector])
AND
((DatePart("q",[DateEntry]))=[Forms]![frmReportSelection]![QuarterSelection]))
ORDER BY DatePart("q",[DateEntry]), Val(DatePart("ww",[DateEntry]));

Any Help???
Thanks in advance