Sort Data on form

[Djblois]

I would like the data that shows on my Continous form to be sorted by a date field. I entered the sort order in the query that the form is based upon but it is still not sorted. How can I do this?

 

[missinglinq]

Is the field actually defined as Date/Time in the underlying table?

Can you list the SQL for your query?

 

[MSAccessRookie]

I would like the data that shows on my Continous form to be sorted by a date field. I entered the sort order in the query that the form is based upon but it is still not sorted. How can I do this?

Please describe what you mean by "it is still not sorted". What you describe is the normal way to do Sort the data in a Continuous Form, unless the Date Field is not a Date Field, and instead is a Text Field that looks like a Date Field. A Text Field would be sorted differently than the real Date Field.

Note: It looks like the Majority Opinion is that you need to validate that the Date is being stored in a Date Field as Opposed to a Text Field. Let us know what you find out.

 

[boblarson]

I would like the data that shows on my Continous form to be sorted by a date field. I entered the sort order in the query that the form is based upon but it is still not sorted. How can I do this?

If it is in the query then it would be sorted properly. If it isn't it may be a text field with a date in it. Can you verify that it actually is stored as DATE/TIME and not as Text?

 

[Djblois]

Yes it is saved as a Date/Time field. Sorry I misspoke it is a Time Field. Actually I tried to sort by a text column also and that didn't work either. it is always showing up in the same order. currently the form is based upon a query that is based on a second query. Here is the SQL for the First Query:

SELECT Scheduled_Appts.Appt_Date, Scheduled_Appts.Carrier, Scheduled_Appts.Sch_Time, Scheduled_Appts.Arr_Time, Scheduled_Appts.Missing_PaperWork, Scheduled_Appts.Incoming, Scheduled_Appts.Appt_ID, Scheduled_Appts.Outgoing, Scheduled_Appts.Dep_Time, Scheduled_Appts.Created_by, Scheduled_Appts.Created_Date, Scheduled_Appts.Last_Modified_by, Scheduled_Appts.Last_Modified_Date, Scheduled_Appts.Confirmation_Num
FROM Scheduled_Appts
WHERE (((Scheduled_Appts.Incoming)=[Forms]![frmScheduled_Appts]![cbViewRec])) OR (((Scheduled_Appts.Outgoing)=[Forms]![frmScheduled_Appts]![cbViewShip]))
ORDER BY Scheduled_Appts.Sch_Time;

Here is the Second Query:

SELECT qryFilterByType.Appt_Date, qryFilterByType.Carrier, qryFilterByType.Sch_Time, qryFilterByType.Arr_Time, qryFilterByType.Dep_Time, qryFilterByType.Missing_PaperWork, qryFilterByType.Incoming, qryFilterByType.Outgoing, qryFilterByType.Appt_ID, qryFilterByType.Created_by, qryFilterByType.Last_Modified_by, qryFilterByType.Last_Modified_Date, qryFilterByType.Confirmation_Num
FROM qryFilterByType
WHERE (((qryFilterByType.Appt_Date)=[Forms]![frmScheduled_Appts]![cldrApptDates]))
ORDER BY qryFilterByType.Sch_Time;

I would also like to do a second sort if this is possible? And I want it so my users can click on a button and sort by a different column. Would I need to created a new Query for that or Can I do it in code?

 

[Djblois]

ok it is working, my fault, but the issue is I wanted to know if I can get the Blanks to sort to the bottom? Also, I want to be able to allow the user to change the sort between Time or Carrier. How do I do this in code?

 

[MSAccessRookie]

ok it is working, my fault, but the issue is I wanted to know if I can get the Blanks to sort to the bottom? Also, I want to be able to allow the user to change the sort between Time or Carrier. How do I do this in code?

Adding the statement "Order by {YourFieldWithBlanks} Descending" should get the Blanks to sort to the bottom.

 

[Djblois]

The issue with that is then the time will also be descending. Well a bigger issue is how do I make it so the user can click on a button and change the sort?

ORDER BY qryFilterByType.Sch_Time, qryFilterByType.Carrier, qryFilterByType.Arr_Time;

That is the SQL statement sometimes I will need Carrier to be the first sort. Depending on what Columns the user Clicks on.

 

[MSAccessRookie]

The issue with that is then the time will also be descending. Well a bigger issue is how do I make it so the user can click on a button and change the sort?

ORDER BY qryFilterByType.Sch_Time, qryFilterByType.Carrier, qryFilterByType.Arr_Time;

That is the SQL statement sometimes I will need Carrier to be the first sort. Depending on what Columns the user Clicks on.

The only way I can think of to do that is either to create a dynamic recordsource that is based on the user selections and updated any time that a change in choices is detected.

 

[Djblois]

What do you mean by create a dynamic RecordSource? Couldn't I just run an SQL statement as a user clicks on the label for the column?

Here is the code that I tried:

Dim strSort As String
    
    strSort = "SELECT Scheduled_Appts.Appt_Date, Scheduled_Appts.Carrier, Scheduled_Appts.Sch_Time, " & _
    "Scheduled_Appts.Arr_Time, Scheduled_Appts.Missing_PaperWork, Scheduled_Appts.Incoming, " & _
    "Scheduled_Appts.Appt_ID, Scheduled_Appts.Outgoing, Scheduled_Appts.Dep_Time, Scheduled_Appts.Created_by, " & _
    "Scheduled_Appts.Created_Date, Scheduled_Appts.Last_Modified_by, Scheduled_Appts.Last_Modified_Date, " & _
    "Scheduled_Appts.Confirmation_Num " & _
    "FROM Scheduled_Appts " & _
    "WHERE ((((Scheduled_Appts.Incoming) = Forms!frmScheduled_Appts!cbViewRec)) Or " & _
    "(((Scheduled_Appts.Outgoing) = Forms!frmScheduled_Appts!cbViewShip))) And " & _
    "(((Scheduled_Appts.Appt_Date) = Forms!frmScheduled_Appts!cldrApptDates)) " & _
    "ORDER BY Scheduled_Appts.Sch_Time, Scheduled_Appts.Arr_Time, Scheduled_Appts.Carrier;"
    
    DoCmd.RunSQL strSort

I thought this might work but it is given me an error

 

[MSAccessRookie]

What do you mean by create a dynamic RecordSource? Couldn't I just run an SQL statement as a user clicks on the label for the column?

Here is the code that I tried:

Dim strSort As String
 
    strSort = "SELECT Scheduled_Appts.Appt_Date, Scheduled_Appts.Carrier, Scheduled_Appts.Sch_Time, " & _
    "Scheduled_Appts.Arr_Time, Scheduled_Appts.Missing_PaperWork, Scheduled_Appts.Incoming, " & _
    "Scheduled_Appts.Appt_ID, Scheduled_Appts.Outgoing, Scheduled_Appts.Dep_Time, Scheduled_Appts.Created_by, " & _
    "Scheduled_Appts.Created_Date, Scheduled_Appts.Last_Modified_by, Scheduled_Appts.Last_Modified_Date, " & _
    "Scheduled_Appts.Confirmation_Num " & _
    "FROM Scheduled_Appts " & _
    "WHERE ((((Scheduled_Appts.Incoming) = Forms!frmScheduled_Appts!cbViewRec)) Or " & _
    "(((Scheduled_Appts.Outgoing) = Forms!frmScheduled_Appts!cbViewShip))) And " & _
    "(((Scheduled_Appts.Appt_Date) = Forms!frmScheduled_Appts!cldrApptDates)) " & _
    "ORDER BY Scheduled_Appts.Sch_Time, Scheduled_Appts.Arr_Time, Scheduled_Appts.Carrier;"
 
    DoCmd.RunSQL strSort

I thought this might work but it is given me an error

You are headed in the right direction, but there is one thing that you need to remember. JET does not know how to parse VB Variables and Form Fields, so you have to include the VALUE to the string, not the Name.

Example:

[/COLOR]
"WHERE ((((Scheduled_Appts.Incoming) = Forms!frmScheduled_Appts!cbViewRec)) Or "
 
Will Become:
 
"WHERE ((((Scheduled_Appts.Incoming) = [B][COLOR=red]'[/COLOR]"[/B] & Forms!frmScheduled_Appts!cbViewRec & [B]"[COLOR=red]'[/COLOR][/B])) Or "[COLOR=black][/COLOR]
[COLOR=black]

 

[boblarson]

Couldn't I just run an SQL statement as a user clicks on the label for the column?

DoCmd.RunSQL strSort

I thought this might work but it is given me an error

Yes, it would because RunSQL is for ACTION queries (Update, Delete, Append) and not for Select queries. For a select query you need to assign it as a Recordset, or use it for something like a DLookup, etc. You can't just "run" it.

 

[Djblois]

Bob,

thank you for helping but how do I assign it as a recordset or use it as a dlookup and which should I use?

 

[Djblois]

Bob,

thank you for helping but how do I assign it as a recordset or use it as a dlookup and which should I use?

How do I do this?

 

[vicsar]

Select the properties sheet, go to the Order by tab an type the name of the field you want to sort by using this format: [Your_Field_Name]

 

[missinglinq]

You realize, of course, that htis thread is over three years old?

 

[vicsar]

You realize, of course, that htis thread is over three years old?

Of course I do, yet three years after this post I was looking for the solution, someone else might be as well. The thread helped me get started and when I found the solution to my problem I thought sharing it would not hurt and was only fair.

So there.