Trouble Sorting multiple columns in datasheet view

[jannecum]

I use Access 97 and I am having a problem sorting multiple columns in the datasheet view of some of my access forms. The forms receive data from a query and I can sort multiple columns in the query view but not in the datasheet view. Any suggestions on where to look for the problem?

I do not write code but usually can do property settings and sorts etc.

Thanks for any help

 

[TheAceMan1]

How are ya jannecum . . .

[blue]The form receives data from a query . . .[/blue]

If this is true you should'nt have this problem. Post back the [blue]RecordSource[/blue] of the form . . . if its a query name, post back the SQL of the query.

or

How is the form receiving data from the query?

See Ya! . . . . . .

 

[jannecum]

This is the Query that feeds my form. In the query itself I can sort multiple fields but when I go to the Dadasheet view of the form I can not sort.


SELECT CIVIL.HISTORY, CIVIL.ID, CIVIL.LastName, CIVIL.FirstName, CIVIL.OpenYear, CIVIL.CaseNo, CIVIL.ClosedYear, CIVIL.STATUS, CIVIL.TransferNo, CIVIL.BoxNo, CIVIL.SentTo, CIVIL.DateOut, CIVIL.RETENTION, CIVIL.LOCATION, CIVIL.COMMENT, CIVIL.CoItemNo, CIVIL.Dept, CIVIL.DeptNo, [ClosedYear]+[RETENTION] AS TARGET, CIVIL.LASTUPDATE, CIVIL.HISTORY, CIVIL.DateIn, CIVIL.AKA
FROM CIVIL
WHERE (((CIVIL.HISTORY) Is Null));

 

[jannecum]

TheAceMan1,

I have the SQL for the above query right in the RecordSource property of the Form. Not sure if this info helps

 

[PHV]

Why not add an ORDER BY clause in your SQL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

jannecum . . .

As indicated by [blue]PHV[/blue], your SQL is not sorted ([blue]No Order By clause[/blue])!

See Ya! . . . . . .

 

[jannecum]

I have to sort many different ways so I don't think setting the order will help.

 

[PHV]

So, have a look at the OrderBy and OrderByOn properties of the form object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jannecum]

I really do not want a predesigned sort - I can do them. I want to highlight datasheet columns that are next to each other and sort all three ascending or decending, but when I try to do this me ascending and decending controls on the menu bar become dim and are not functioning.

 

[jannecum]

These are the Help instructions for what I am trying to do: It says you can sort adjacent columns but my asending/decending controls become inoperable when I select adjacent columns


Sort records in Form or Datasheet view

For a list of issues to consider when sorting records, click .

1 In Form view or Datasheet view, click the field you want to use for sorting records.
2 Do one of the following:

· To sort in ascending order, click Sort Ascending .
· To sort in descending order, click Sort Descending .

Note In a form, you can sort on only one field at a time; in a datasheet, you can select two or more adjacent columns at the same time, and then sort them. Microsoft Access sorts records starting with the leftmost selected column. When you save the form or datasheet, Microsoft Access saves the sort order.

 

[TheAceMan1]

jannecum . . .

Realize . . . what you need requires an [blue]Dynamic SQL,[/blue] where you [blue]SELECT[/blue] fields in [blue]proper order[/blue], followed by a [blue]synchronized ORDER BY clause[/blue] ([purple]mixed ascending/decending[/purple]).

The problem is you can't present the necessary options for sorting in DataSheet View! . . . they won't show!

Before I attempt to take this on . . . you need to:
[ol][li]Switch to [blue]Continuous Form[/blue] view (you can make a continuous view form look just like a datasheet!).[/li]
[li]Then in the header/footer of the form (your choice) you need [blue]4 comboboxes[/blue], with which to select the [blue]priority order of sorting[/blue] (labeled as such), along with a Checkbox to indicate [blue]Ascending/Decending sort order[/blue] (for each combo!). With this info you can [blue]dynamically reconstruct[/blue] the SQL of the [blue]RecordSource[/blue] of the form proper.[/li]
[li]All this along with a [blue]Sort Button[/blue] to instantiate the new dynamic SQL![/li][/ol]
[purple]Your thoughts![/purple]

See Ya! . . . . . .

 

[jannecum]

For the application I use, pre-determining any sort criteria in the datasheet view of the form will not assist me in the variety of sort orders I need to do. I do use preset sorts in my reports and in other forms where I do use the setting for continous forms, but I do want the option of sorting multiple datasheet columns as the help text above indicates in the note I printed previously - i.e. "in a datasheet, you can select two or more adjacent columns at the same time, and then sort them"; I do have some forms where this direction works but others that will not and I see no difference in the forms properties.

I currently have someone in IT checking to see why it is not allowed in some of my forms but is in others... I do appreciate your input. Thank you.

 

[jannecum]

TheAceMan1,

I finally got it. a simple thing - My error was: If you have any of the fields tab properties set to no tab it does not allow the column to be sorted in the datasheet view. So if sorting is required the way I do it all tabs should be "yes".

Thanks again.