Another sorting issue....

[sondriven]

I did a search and am having a hard time finding out how to sort 2 fields in the same query.

I have a subform that is based on a query. I need it to sort by PO# first and then by Ship Date second.

Thanks

 

[glynch]

The order of the fields in the "order by" clause determines the sort order. Ex:

SELECT tblClass.ClassID, tblClass.ClassName, tblClass.ClassPrefix, tblClass.FieldType
FROM tblClass
ORDER BY tblClass.ClassName, tblClass.ClassPrefix;

 

[sondriven]

Thanks for the reply. Heres exactly what I have. One table "On Time Shipping Subform" and a bunch of fields. I entered this into the Order By Property. After I save it, it disappears and nothing is sorted. Am I missing something. Seems like there should be brackets or something.



SELECT On Time Shipping Subform.ID, On Time Shipping Subform.Purchase Order, On Time Shipping Subform.Control Numbers, On Time Shipping Subform.Required Date, On Time Shipping Subform.Quantity Required, On Time Shipping Subform.Actual Date, On Time Shipping Subform.Quantity Actual Ship, On Time Shipping Subform.On Time, On Time Shipping Subform.Pedmic Caused Late, On Time Shipping Subform.Cust Caused Late, On Time Shipping Subform.Notes, On Time Shipping Subform.Product ID FROM On Time Shipping Subform ORDER BY On Time Shipping Subform.Purchase Order, On Time Shipping Subform.Required Ship Date;


Thanks.

 

[glynch]

I have a suggestion about table and field names. A couple of common practices are Hungarian Notation, concatenate all of the words together with each word capitalized (ex. TableName, FieldName), another is using undercores between words (table_name, field_name). The spaces you have within your object names probably is confusing the interpretation of your sql. Words like 'Date' are reserved words and Access will interpret that as today's date. If you want to have the spaces you need to bracket your object names so that Access sees them for what they are (ex. [On Time Shipping Subform].[Purchase Order]).

Try that first with your names as they are and see if your sql will run.

 

[sondriven]

Good news and Bad news.

The Statement is now staying in the Order By Box, but its not sorting. Heres what Ive tried:

SELECT ([On Time Shipping Subform].[ID]), ([On Time Shipping Subform].[Purchase Order]), ([On Time Shipping Subform].[Control Numbers]), ([On Time Shipping Subform].[Required Date]), ([On Time Shipping Subform].[Quantity Required]), ([On Time Shipping Subform].[Actual Date]), ([On Time Shipping Subform].[Quantity Actual Ship]), ([On Time Shipping Subform].[On Time]), ([On Time Shipping Subform].[Pedmic Caused Late]), ([On Time Shipping Subform].[Cust Caused Late]), ([On Time Shipping Subform].[Notes]), ([On Time Shipping Subform].[Product ID]) FROM [On Time Shipping Subform] ORDER BY ([On Time Shipping Subform].[Purchase Order]), ([On Time Shipping Subform].[Required Ship Date]);

Other than this statement do I need to put anything into the sort boxes? Like Ascending or Descending?

Thanks

 

[simongallop]

Looking at your statement you are trying to sort by a field that isn't there. In the select statement the field is Required Date whereas in your order statement it is Required Ship Date. Do you have a field elsewhere in On Tim Shipping Subform that is called Required Ship Date?

 

[glynch]

It will sort in ascending order by default. I don't see the [On Time Shipping Subform].[Required Ship Date] in the SELECT clause of your SQL statement, so it could be ordering the records in a manner that isn't evident in the results. Is it sorting on the [Purchase Order] field at least?

Maybe you should try constructing the query in the design mode where you can do it graphically, this would help avoid any syntax errors. You can just pull the fields from the little table field list in the upper half down into the query fields section in the lower half and pick the sort fields using the dropdowns.

Reply again to this post if you are not familiar with creating queries like this. It is much easier than writing SQL statements from scratch, especially if you type as poorly as I do.

HTH

 

[sondriven]

Yes, typo error. Got it now. Thanks Much!