Query in Open_Form event

  • Thread starter Thread starter midnite oit
  • Start date Start date
M

midnite oit

Hello,

This query was originally put in the RecordSource in the report's properties
sheet. The results are good and sorted by Year followed by month.

I modified the report and to put this query in code in the on_open form
event and have the Report.RecordSource = strSQL. The results in the report
are not ordered by Year, Month. I have a breakpoint in the code and am able
to get the strSQL statement from the immediate window, cut and paste in the
query window and it works fine - the results are ordered by Year, Month.

"SELECT [Month Query].Year, [Month Query].Month, Sum([Month
Query].NumberInitial) AS [Number of Reports], Months.MonthLong, Sum([Month
Query].NumberFU) AS [Number of FU Reports]
FROM [Month Query] INNER JOIN Months ON [Month Query].Month=Months.Month
GROUP BY [Month Query].Year, [Month Query].Month, Months.MonthLong
ORDER BY [Month Query].Year DESC , [Month Query].Month DESC;"

My work around is define the sort order in the Grouping and Sorting box and
that works fine for me.

Does anyone know and would appreciate your explanation on this different
behavior.
 
Reports are only reliably sorted using the Sorting and Grouping dialog in
the report design (or code that sets the values).

Do I dare ask how you are using your Month table?
 
Hi Duane,

Thank you and I appreciate your responding....

The following is part of the query I first posted. The original report has
the query defined in the properties sheet "RecordSource" and the results
returned in sorted order. No Grouping and Sorting is done on the report.
=================================================
[Month Query] :

SELECT DatePart("yyyy",[IntReports.Date]) AS [Year],
DatePart("m",[IntReports.Date]) AS [Month],
[IntReports].[Date],
[IntReports].[NumberFU],
[IntReports].[NumberInitial]
FROM IntReports
WHERE ([IntReports].[Date])>=[Forms]![StatsForm]![DateFirst] And
([IntReports].[DateD])<=[Forms]![StatsForm]![DateLast] ORDER BY
[IntReports].[Date];

Month table : has 3 fields ID, Month (number), MonthLong (text)

====================================================

When I converted it to code in the Open_Report event, I have it re-written
like this. If i were to put in breakpoints and display the strReportSQL
string, cut and paste it in a Query window, the results return in sorted
order YEAR->MONTH. However, in the report, it just does not behave the
same.

Like you have said, I put in the Grouping and Sorting definition in the
report and it works fine.

strReportSQL = "SELECT [TEMP].[Year], " & _
" [TEMP].[Month], " & _
" Sum([TEMP].[NumberInitial]) AS [Number of
Reports], " & _
" [tbl_Month]." & Trim(gsDescriptionField) & "
AS [MonthLong], " & _
" Sum([TEMP].[NumberFU]) AS [Number of FU
Reports] " & _
" FROM ( " & _
" ( SELECT
DatePart('yyyy',[IntReports.Date]) AS [Year], " & _
"
DatePart('m',[IntReports.Date]) AS [Month], " & _
" IntReports.Date , " & _
" IntReports.NumberFU , " & _
" IntReports.NumberInitial " &
_
" FROM IntReports " & _
" WHERE IntReports.Date >= #" &
Forms!StatsForm!DateFirst & "# " & _
" AND IntReports.Date <= #" &
Forms!StatsForm!DateLast & "# " & _
" ORDER BY IntReports.Date " & _
" ) AS TEMP " & _
" ) " & _
" INNER JOIN [tbl_Month] " & _
" ON [TEMP].[Month]=[tbl_Month].[Month
Number] " & _
" GROUP BY [TEMP].[Year], " & _
" [TEMP].[Month], " & _
" [tbl_Month]." & Trim(gsDescriptionField)
& " " & _
" ORDER BY [TEMP].[Year] DESC , [TEMP].[Month]
DESC; "

Report.RecordSource = strReportSQL




Duane Hookom said:
Reports are only reliably sorted using the Sorting and Grouping dialog in
the report design (or code that sets the values).

Do I dare ask how you are using your Month table?

--
Duane Hookom
MS Access MVP
--

midnite oit said:
Hello,

This query was originally put in the RecordSource in the report's
properties
sheet. The results are good and sorted by Year followed by month.

I modified the report and to put this query in code in the on_open form
event and have the Report.RecordSource = strSQL. The results in the
report
are not ordered by Year, Month. I have a breakpoint in the code and am
able
to get the strSQL statement from the immediate window, cut and paste in
the
query window and it works fine - the results are ordered by Year, Month.

"SELECT [Month Query].Year, [Month Query].Month, Sum([Month
Query].NumberInitial) AS [Number of Reports], Months.MonthLong, Sum([Month
Query].NumberFU) AS [Number of FU Reports]
FROM [Month Query] INNER JOIN Months ON [Month Query].Month=Months.Month
GROUP BY [Month Query].Year, [Month Query].Month, Months.MonthLong
ORDER BY [Month Query].Year DESC , [Month Query].Month DESC;"

My work around is define the sort order in the Grouping and Sorting box
and
that works fine for me.

Does anyone know and would appreciate your explanation on this different
behavior.
Click to expand...
Click to expand...
 
Again, if you want to sort or group a report, use the Sorting and Grouping
dialog in the report. You can remove any ORDER BY in your query since it is
not reliable in a report display. If you need to dynamically change the
sorting in a report, see Allen Browne's web site
http://members.iinet.net.au/~allenbrowne/ser-33.html.

--
Duane Hookom
MS Access MVP
--

midnite oil said:
Hi Duane,

Thank you and I appreciate your responding....

The following is part of the query I first posted. The original report
has
the query defined in the properties sheet "RecordSource" and the results
returned in sorted order. No Grouping and Sorting is done on the report.
=================================================
[Month Query] :

SELECT DatePart("yyyy",[IntReports.Date]) AS [Year],
DatePart("m",[IntReports.Date]) AS [Month],
[IntReports].[Date],
[IntReports].[NumberFU],
[IntReports].[NumberInitial]
FROM IntReports
WHERE ([IntReports].[Date])>=[Forms]![StatsForm]![DateFirst] And
([IntReports].[DateD])<=[Forms]![StatsForm]![DateLast] ORDER BY
[IntReports].[Date];

Month table : has 3 fields ID, Month (number), MonthLong (text)

====================================================

When I converted it to code in the Open_Report event, I have it re-written
like this. If i were to put in breakpoints and display the strReportSQL
string, cut and paste it in a Query window, the results return in sorted
order YEAR->MONTH. However, in the report, it just does not behave the
same.

Like you have said, I put in the Grouping and Sorting definition in the
report and it works fine.

strReportSQL = "SELECT [TEMP].[Year], " & _
" [TEMP].[Month], " & _
" Sum([TEMP].[NumberInitial]) AS [Number of
Reports], " & _
" [tbl_Month]." & Trim(gsDescriptionField) &
"
AS [MonthLong], " & _
" Sum([TEMP].[NumberFU]) AS [Number of FU
Reports] " & _
" FROM ( " & _
" ( SELECT
DatePart('yyyy',[IntReports.Date]) AS [Year], " & _
"
DatePart('m',[IntReports.Date]) AS [Month], " & _
" IntReports.Date , " & _
" IntReports.NumberFU , " & _
" IntReports.NumberInitial "
&
_
" FROM IntReports " & _
" WHERE IntReports.Date >= #" &
Forms!StatsForm!DateFirst & "# " & _
" AND IntReports.Date <= #" &
Forms!StatsForm!DateLast & "# " & _
" ORDER BY IntReports.Date " & _
" ) AS TEMP " & _
" ) " & _
" INNER JOIN [tbl_Month] " & _
" ON [TEMP].[Month]=[tbl_Month].[Month
Number] " & _
" GROUP BY [TEMP].[Year], " & _
" [TEMP].[Month], " & _
" [tbl_Month]." &
Trim(gsDescriptionField)
& " " & _
" ORDER BY [TEMP].[Year] DESC , [TEMP].[Month]
DESC; "

Report.RecordSource = strReportSQL




Duane Hookom said:
Reports are only reliably sorted using the Sorting and Grouping dialog in
the report design (or code that sets the values).

Do I dare ask how you are using your Month table?

--
Duane Hookom
MS Access MVP
--

midnite oit said:
Hello,

This query was originally put in the RecordSource in the report's
properties
sheet. The results are good and sorted by Year followed by month.

I modified the report and to put this query in code in the on_open form
event and have the Report.RecordSource = strSQL. The results in the
report
are not ordered by Year, Month. I have a breakpoint in the code and am
able
to get the strSQL statement from the immediate window, cut and paste in
the
query window and it works fine - the results are ordered by Year,
Month.

"SELECT [Month Query].Year, [Month Query].Month, Sum([Month
Query].NumberInitial) AS [Number of Reports], Months.MonthLong, Sum([Month
Query].NumberFU) AS [Number of FU Reports]
FROM [Month Query] INNER JOIN Months ON [Month
Query].Month=Months.Month
GROUP BY [Month Query].Year, [Month Query].Month, Months.MonthLong
ORDER BY [Month Query].Year DESC , [Month Query].Month DESC;"

My work around is define the sort order in the Grouping and Sorting box
and
that works fine for me.

Does anyone know and would appreciate your explanation on this
different
behavior.
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

will do...once again thank you for link info and appreciate your response.


Duane Hookom said:
Again, if you want to sort or group a report, use the Sorting and Grouping
dialog in the report. You can remove any ORDER BY in your query since it is
not reliable in a report display. If you need to dynamically change the
sorting in a report, see Allen Browne's web site
http://members.iinet.net.au/~allenbrowne/ser-33.html.

--
Duane Hookom
MS Access MVP
--

midnite oil said:
Hi Duane,

Thank you and I appreciate your responding....

The following is part of the query I first posted. The original report
has
the query defined in the properties sheet "RecordSource" and the results
returned in sorted order. No Grouping and Sorting is done on the report.
=================================================
[Month Query] :

SELECT DatePart("yyyy",[IntReports.Date]) AS [Year],
DatePart("m",[IntReports.Date]) AS [Month],
[IntReports].[Date],
[IntReports].[NumberFU],
[IntReports].[NumberInitial]
FROM IntReports
WHERE ([IntReports].[Date])>=[Forms]![StatsForm]![DateFirst] And
([IntReports].[DateD])<=[Forms]![StatsForm]![DateLast] ORDER BY
[IntReports].[Date];

Month table : has 3 fields ID, Month (number), MonthLong (text)

====================================================

When I converted it to code in the Open_Report event, I have it re-written
like this. If i were to put in breakpoints and display the strReportSQL
string, cut and paste it in a Query window, the results return in sorted
order YEAR->MONTH. However, in the report, it just does not behave the
same.

Like you have said, I put in the Grouping and Sorting definition in the
report and it works fine.

strReportSQL = "SELECT [TEMP].[Year], " & _
" [TEMP].[Month], " & _
" Sum([TEMP].[NumberInitial]) AS [Number of
Reports], " & _
" [tbl_Month]." & Trim(gsDescriptionField) &
"
AS [MonthLong], " & _
" Sum([TEMP].[NumberFU]) AS [Number of FU
Reports] " & _
" FROM ( " & _
" ( SELECT
DatePart('yyyy',[IntReports.Date]) AS [Year], " & _
"
DatePart('m',[IntReports.Date]) AS [Month], " & _
" IntReports.Date , " & _
" IntReports.NumberFU , " & _
" IntReports.NumberInitial "
&
_
" FROM IntReports " & _
" WHERE IntReports.Date >= #" &
Forms!StatsForm!DateFirst & "# " & _
" AND IntReports.Date <= #" &
Forms!StatsForm!DateLast & "# " & _
" ORDER BY IntReports.Date " & _
" ) AS TEMP " & _
" ) " & _
" INNER JOIN [tbl_Month] " & _
" ON [TEMP].[Month]=[tbl_Month].[Month
Number] " & _
" GROUP BY [TEMP].[Year], " & _
" [TEMP].[Month], " & _
" [tbl_Month]." &
Trim(gsDescriptionField)
& " " & _
" ORDER BY [TEMP].[Year] DESC , [TEMP].[Month]
DESC; "

Report.RecordSource = strReportSQL




Duane Hookom said:
Reports are only reliably sorted using the Sorting and Grouping dialog in
the report design (or code that sets the values).

Do I dare ask how you are using your Month table?

--
Duane Hookom
MS Access MVP
--

Hello,

This query was originally put in the RecordSource in the report's
properties
sheet. The results are good and sorted by Year followed by month.

I modified the report and to put this query in code in the on_open form
event and have the Report.RecordSource = strSQL. The results in the
report
are not ordered by Year, Month. I have a breakpoint in the code and am
able
to get the strSQL statement from the immediate window, cut and paste in
the
query window and it works fine - the results are ordered by Year,
Month.

"SELECT [Month Query].Year, [Month Query].Month, Sum([Month
Query].NumberInitial) AS [Number of Reports], Months.MonthLong, Sum([Month
Query].NumberFU) AS [Number of FU Reports]
FROM [Month Query] INNER JOIN Months ON [Month
Query].Month=Months.Month
GROUP BY [Month Query].Year, [Month Query].Month, Months.MonthLong
ORDER BY [Month Query].Year DESC , [Month Query].Month DESC;"

My work around is define the sort order in the Grouping and Sorting box
and
that works fine for me.

Does anyone know and would appreciate your explanation on this
different
behavior.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top