Sort data on an Access SubForm

[steve_colman]

Hi All,

I have a form with about 4 subforms in a tab control.

The subform in the first tab control tab does not sort the data by the correct field.

The subform itself and the table the data is from sort the data correctly. When you open the subform or the table the data is displayed in the correct oder - which is by the time the task needs to be run.

When you open the subform within the form it is placed in, the order gets messed up.

Anyone know how to solve this?

Thanks

Steve

 

[Joe4]

How about if you send the table to a query first, where you can specifically set your sort order, then run the subform off of thiq query instead of the table?

 

[steve_colman]

I have tried using a query as the data source for the subform, sorting the data by the required field. But get the same issue wqhen the subform is opened using the main form.

Any other ideas?

Thanks

 

[Joe4]

Are you doing some sort of sorting on the Subform level, ar are you doing anything with Grouping?

Can you explain what field you are trying to sort by, and what field it is actually sorting by? Giving some examples may help...

 

[steve_colman]

The subform is used to list a number of daily tasks which need to be completed.

This tasks are sorted in the source table by the time required field (Date/Time - set to short time).

These tasks are also sorted by the time required field in the subform and the query which joins the two.

There is no grouping used. I have also tried just using the table as the data source - with the same issue.

If the subform is opened on its own the data is display correctly.

When the subform is opened in the main form the ordering is lost. There is no sorting or grouping used by the main form.

Let me know what other information you would need to figure this out.

Thanks

 

[Joe4]

The only other thing I have to add is this:

If using Date/Time fields, just be wary that just because you limit a field to show the time doesn't mean that Access doesn't remember the date portion of the field.

For example, if you have the following date/time fields:
2004/05/10 4:39:00 PM
2004/05/11 8:12:00 AM

If you just show the times, you will have:
4:39:00 PM
8:12:00 AM

I believe that when Access sorts, it will put the 4:39 PM before the 8:12 AM because it remembers that it is for an earlier date.

You may want to do another query and expand your time field to show the date also to see if this is what is happening.

 

[steve_colman]

To avoid all issue with this kind of field I have also tried chaning the field type to text. With the same messed up result.

Cheers for you replies!

 

[Joe4]

Well, you have me stumped! If you post a small sample of your data, and them list how the records are being sorted, I might be able to see a pattern (though I am not too optimistic, as it sounds like you accounted for the things I was talking about).

I have taken to importing dates as text also because I need to match on date fields, and it was tacking on a time to my dates, so my date relationships weren't working properly.

Date and times can really cause a lot of headaches in Access!

 

[steve_colman]

Sample of data

Task_ID,Task_Name,Task_Description,Team_Name,Task_Deadline
2, Held Trades clear?, Earlies, 14:00,


The are other feilds but not needed. The data seems to be sorting by the Task_Name field.

Any ideas why? There is nothing anywhere saying to do that.

Cheers again

 

[Joe4]

A few other thoughts:

1. You said that your times are entered as Text. Are you tacking on leading zeroes, i.e. 08:00 so that 08:00 is sorted before 11:00 (if it is entered as 8:00, it will not sort right)?

2. Just for the heck of it, in your query, try moving the field you want to sort by (time) to the first field in the query. If, for some reason, the subform is trying to sort by the first field in the Data Source, this might rectify the situation.