Weird Report Problem - Access 2003

[manning_news]

I've got a report that's not sorting correctly. I build a SQL
statement and assign it to the recordsource in the Open event, sorting
the data the way the user chooses. The user can choose up to 3 sorts
using a form that opens before the report opens.

When the report previews, it ignores the users' sort request and always
sorts by the primary key even though ORDER BY is explicity set to
another field other than primary key.

If I copy the SQL into a query, then it sorts the way the user
requests.

If I comment out the Open event and apply the SQL statement directly in
the Record Source property, the report still sorts only by primary key
even though ORDER BY is explicity set.

The report consists of a report header/footer, page header/footer, and
detail. Grouping and Sorting is not set.

There is another report that is very similar to this one and it works
just fine.

Any ideas on why this is happening? I recreated the report - no
change. I even recreated the front-end thinking there was some
corruption - no change. I converted the mdb to 2000 - no change.

[Allen Browne]

My first thought would be that Access is misunderstanding the data type of
the column you are expecting it to sort by.

This could happen if:
a) You saved the report with some other RecordSource. Best to save with none
if you are planning to assign it in Report_Open.

b) The field you are sorting on is a calculated field.
To avoid this, typecast the calculation with CVDate().
More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

BTW, you did include:
Me.OrderByOn = True
in Report_Open *after* assigning the RecordSource?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I've got a report that's not sorting correctly. I build a SQL
statement and assign it to the recordsource in the Open event, sorting
the data the way the user chooses. The user can choose up to 3 sorts
using a form that opens before the report opens.

When the report previews, it ignores the users' sort request and always
sorts by the primary key even though ORDER BY is explicity set to
another field other than primary key.

If I copy the SQL into a query, then it sorts the way the user
requests.

If I comment out the Open event and apply the SQL statement directly in
the Record Source property, the report still sorts only by primary key
even though ORDER BY is explicity set.

The report consists of a report header/footer, page header/footer, and
detail. Grouping and Sorting is not set.

There is another report that is very similar to this one and it works
just fine.

Any ideas on why this is happening? I recreated the report - no
change. I even recreated the front-end thinking there was some
corruption - no change. I converted the mdb to 2000 - no change.

[manning_news]

Thanks for your reply.

No, Record Source is blank. No, none of the sortable fields are
calculated fields; they're all text. I didn't put in Me.OrderByOn =
True initially, but it made no difference when I did.

Here's what I did find though. The SQL statement combines 2 tables.
When I eliminated the 2nd table from the statement, then the report
sorted just fine. Then I added just one field from the 2nd table and
the sort is ignored again. So there's something in the SQL statement
that the report doesn't like but the query is OK with. I'm not sure
how many times in the past I've build SQL statements like this
combining multiple tables. This is the first time to my knowledge that
a report has ever done this.


Allen Browne wrote:

My first thought would be that Access is misunderstanding the data type of
the column you are expecting it to sort by.

This could happen if:
a) You saved the report with some other RecordSource. Best to save with none
if you are planning to assign it in Report_Open.

b) The field you are sorting on is a calculated field.
To avoid this, typecast the calculation with CVDate().
More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

BTW, you did include:
Me.OrderByOn = True
in Report_Open *after* assigning the RecordSource?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I've got a report that's not sorting correctly. I build a SQL
statement and assign it to the recordsource in the Open event, sorting
the data the way the user chooses. The user can choose up to 3 sorts
using a form that opens before the report opens.

When the report previews, it ignores the users' sort request and always
sorts by the primary key even though ORDER BY is explicity set to
another field other than primary key.

If I copy the SQL into a query, then it sorts the way the user
requests.

If I comment out the Open event and apply the SQL statement directly in
the Record Source property, the report still sorts only by primary key
even though ORDER BY is explicity set.

The report consists of a report header/footer, page header/footer, and
detail. Grouping and Sorting is not set.

There is another report that is very similar to this one and it works
just fine.

Any ideas on why this is happening? I recreated the report - no
change. I even recreated the front-end thinking there was some
corruption - no change. I converted the mdb to 2000 - no change.

[manning_news]

OK, found the problem. In the report footer, I sum 3 fields from the
2nd table. If I take those sum's out, then the report sorts fine. Why
would these make a difference?

[Allen Browne]

The OrderByOn is essential, and you must place it after the assignment of
the RecordSource.

Adding a 2nd table should not make a difference.

You could try putting one field in the report's Sorting'n'Grouping dialog.
Then in Report_Open, reassign this to the appropriate field by setting the
ControlSource of the GroupLevel. Example in:
Sorting Records in a Report at runtime
at:
http://allenbrowne.com/ser-33.html

You're sure this is a Date/Time field in the table (not a text field that
looks like a date)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Thanks for your reply.

No, Record Source is blank. No, none of the sortable fields are
calculated fields; they're all text. I didn't put in Me.OrderByOn =
True initially, but it made no difference when I did.

Here's what I did find though. The SQL statement combines 2 tables.
When I eliminated the 2nd table from the statement, then the report
sorted just fine. Then I added just one field from the 2nd table and
the sort is ignored again. So there's something in the SQL statement
that the report doesn't like but the query is OK with. I'm not sure
how many times in the past I've build SQL statements like this
combining multiple tables. This is the first time to my knowledge that
a report has ever done this.
Allen Browne wrote:

My first thought would be that Access is misunderstanding the data type
of
the column you are expecting it to sort by.

This could happen if:
a) You saved the report with some other RecordSource. Best to save with
none
if you are planning to assign it in Report_Open.

b) The field you are sorting on is a calculated field.
To avoid this, typecast the calculation with CVDate().
More info in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

BTW, you did include:
Me.OrderByOn = True
in Report_Open *after* assigning the RecordSource?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

> I've got a report that's not sorting correctly. I build a SQL
> statement and assign it to the recordsource in the Open event, sorting
> the data the way the user chooses. The user can choose up to 3 sorts
> using a form that opens before the report opens.
>
> When the report previews, it ignores the users' sort request and always
> sorts by the primary key even though ORDER BY is explicity set to
> another field other than primary key.
>
> If I copy the SQL into a query, then it sorts the way the user
> requests.
>
> If I comment out the Open event and apply the SQL statement directly in
> the Record Source property, the report still sorts only by primary key
> even though ORDER BY is explicity set.
>
> The report consists of a report header/footer, page header/footer, and
> detail. Grouping and Sorting is not set.
>
> There is another report that is very similar to this one and it works
> just fine.
>
> Any ideas on why this is happening? I recreated the report - no
> change. I even recreated the front-end thinking there was some
> corruption - no change. I converted the mdb to 2000 - no change.

[Allen Browne]

That suggests that the report's sorting is *undefined*, i.e. the OrderByOn
is not working. Try setting the Control Source of the GroupLevel so the
sorting is defined.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

OK, found the problem. In the report footer, I sum 3 fields from the
2nd table. If I take those sum's out, then the report sorts fine. Why
would these make a difference?

[manning_news]

OK, think I got it by setting GroupLevel(n).ControlSource. At least
the report is sorting the way the user requests. Now, I had to "fudge"
some entries in Sorting and Grouping dialog to get the GroupLevel(n)
coding to work. Is that what you're supposed to do? Is that what you
meant by "you just need to make sure that you have set up the right
number of grouping levels in the report's grouping and sorting dialog"
in your tip?

Thanks for your help...

[Allen Browne]

Yes, if you want to specify 2 levels of sorting, then you need to put 2
entries in the Sortin'nGrouping dialog at design time so you can manipulate
them at runtime.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

OK, think I got it by setting GroupLevel(n).ControlSource. At least
the report is sorting the way the user requests. Now, I had to "fudge"
some entries in Sorting and Grouping dialog to get the GroupLevel(n)
coding to work. Is that what you're supposed to do? Is that what you
meant by "you just need to make sure that you have set up the right
number of grouping levels in the report's grouping and sorting dialog"
in your tip?

Thanks for your help...