sort order of the date is "slightly" off...

[markjkubicki]

I want to sort this query first by last 'date visited'; then by 'times visited'

the problem seems to be the way the date is sorted
in descendnig order, I get:
6/8/2015
6/5/2015
6/4/2015


6/12/2015

it seems to be sorting the date as text, NOT as a date
so I poked around, and change the query to adress date sorting (I thought)...
I suspect, as my grandmother would say: "right church, wrong pew..."

here's the SQL

SELECT
[tblChangeProjectList].[ProjectName],
[tblChangeProjectList].[FilePath],
[tblChangeProjectList].[FilePath],
[tblChangeProjectList].[TimesVisited],
TO_CHAR( [tblChangeProjectList].[LastTimeVisited],'YYYYMMDD') LastTimeVisited
FROM tblChangeProjectList ORDER BY [ProjectName]
UNION Select "< Pick from List...>", "<PICK...>", "", "", ""
FROM [tblChangeProjectList]
ORDER BY LastTimeVisited DESC , [tblChangeProjectList].[TimesVisited];


many thanks in advance,
Mark

[Bob Fitz]

Perhaps you need to replace TO_CHAR with TO_DATE

[JoeM]

the problem seems to be the way the date is sorted
in descendnig order, I get:
6/8/2015
6/5/2015
6/4/2015
6/12/2015

it seems to be sorting the date as text, NOT as a date

Actually, you can do this pretty simply using the DateValue function in your "Order By" clause like this:
SELECT
[tblChangeProjectList].[ProjectName],
[tblChangeProjectList].[FilePath],
[tblChangeProjectList].[FilePath],
[tblChangeProjectList].[TimesVisited],
[tblChangeProjectList].[LastTimeVisited]
FROM tblChangeProjectList
ORDER BY [ProjectName],
DateValue([LastTimeVisited]) DESC

[markjkubicki]

I forgot to include that the feild is already stored in a date format (general date format)

[markjkubicki]

chonged to be:

SELECT [tblChangeProjectList].[ProjectName], [tblChangeProjectList].[FilePath], [tblChangeProjectList].[FilePath], [tblChangeProjectList].[TimesVisited], [tblChangeProjectList].[LastTimeVisited] FROM tblChangeProjectList ORDER BY [ProjectName] UNION Select "< Pick from List...>", "<PICK...>", "", "", "" FROM [tblChangeProjectList]
ORDER BY DateValue([tblChangeProjectList].[LastTimeVisited]) DESC , [tblChangeProjectList].[TimesVisited];

which results in the error:
includes fields that are not serected by the query...

[JoeM]

I forgot to include that the feild is already stored in a date format (general date format)

You may want to check that again. If the field is truly stored as a Date data type, the sorting would work correctly (it would sort be date).
The way you have described it, it is being sorted as if it were a text field.

However, in looking back at your original query, it looks like you are sorting by ProjectName first, and then your Date field. Maybe that is what is confusing you.
Try just sorting by the date field, and see what it looks like (see code below). If it truly a date field, it won't sort like you have described in your original post.

SELECT 
[tblChangeProjectList].[ProjectName], 
[tblChangeProjectList].[FilePath], 
[tblChangeProjectList].[FilePath], 
[tblChangeProjectList].[TimesVisited], 
[tblChangeProjectList].[LastTimeVisited]
FROM tblChangeProjectList 
ORDER BY [LastTimeVisited] DESC

[JoeM]

SELECT [tblChangeProjectList].[ProjectName], [tblChangeProjectList].[FilePath], [tblChangeProjectList].[FilePath], [tblChangeProjectList].[TimesVisited], [tblChangeProjectList].[LastTimeVisited] FROM tblChangeProjectList ORDER BY [ProjectName] UNION Select "< Pick from List...>", "<PICK...>", "", "", "" FROM [tblChangeProjectList]
ORDER BY DateValue([tblChangeProjectList].[LastTimeVisited]) DESC , [tblChangeProjectList].[TimesVisited];

which results in the error:
includes fields that are not serected by the query...

What exactly are you trying to do with the "UNION" part?
Try removing it for now, to get the rest working properly. When you have that part working well, then we can talk about what you are trying to do there.

[markjkubicki]

checked... it's definely a date
also deleted the order by project name

still...

SELECT [tblChangeProjectList].[ProjectName], [tblChangeProjectList].[FilePath], [tblChangeProjectList].[FilePath], [tblChangeProjectList].[TimesVisited], [tblChangeProjectList].[LastTimeVisited] FROM tblChangeProjectList UNION Select "< Pick from List...>", "<PICK...>", "", "", "" FROM [tblChangeProjectList]
ORDER BY [tblChangeProjectList].[LastTimeVisited] DESC , [tblChangeProjectList].[TimesVisited];

[markjkubicki]

the union part allow me to add the value <pick from list) for those cases when the value isn't already stored in the table [tblChangeProjectList] (which is simple a list of any of the projects that they have visited in the last 30days...), the user has the option of going to another loctation to pick a value

[JoeM]

As I suggested in the previous post, try it without the UNION query first.

SELECT 
[tblChangeProjectList].[ProjectName], 
[tblChangeProjectList].[FilePath], 
[tblChangeProjectList].[TimesVisited], 
[tblChangeProjectList].[LastTimeVisited] 
FROM tblChangeProjectList 
ORDER BY 
[tblChangeProjectList].[LastTimeVisited] DESC, 
[tblChangeProjectList].[TimesVisited];

When you run into these type of situations, simplify first, get it working correctly, and then build upon that.

[markjkubicki]

deleted the union clause, and it worked:

SELECT
[tblChangeProjectList].[ProjectName],
[tblChangeProjectList].[FilePath],
[tblChangeProjectList].[FilePath],
[tblChangeProjectList].[TimesVisited],
[tblChangeProjectList].[LastTimeVisited]
FROM tblChangeProjectList
ORDER BY LastTimeVisited DESC , [tblChangeProjectList].[TimesVisited];

[markjkubicki]

could the problem be that the correlaing feild in the union statement is: "" -which is a text value, at it is being unioned to a"date" feild which is the sort...

so the field consists of both date and text - which is throwing the sort into a tizzy
the question is then: how do i add a 'fake' date value to the union...

[JoeM]

Yes, I think that is exactly what is going on. You need to UNION fields of the same data type, or else it will probably convert them to text.

So I would recommend adding some dummy date field in there, i.e.

...UNION Select "< Pick from List...>", "<PICK...>", "", "", DateSerial(2099,12,31)...

[markjkubicki]

BANG... that was it!!!
big thnx

SELECT [tblChangeProjectList].[ProjectName], [tblChangeProjectList].[FilePath], [tblChangeProjectList].[TimesVisited], [tblChangeProjectList].[LastTimeVisited] FROM tblChangeProjectList ORDER BY [ProjectName] UNION Select "< Pick from List...>", "<PICK...>", 1, DateSerial(2099,12,31) FROM [tblChangeProjectList] ORDER BY [tblChangeProjectList].[LastTimeVisited] DESC , [tblChangeProjectList].[TimesVisited];

[JoeM]

You are welcome. Glad we got it all working!