Strange Query Design Grid Behavior

[gr8dane]

I've created a number of databases for my personal use over the past three decades, mostly in mdb format, some converted to accdb as needed. The one I'm currently creating is the first one that's accdb from the start and I'm seeing some behavior I've never seen before.

When designing a query and setting a sort order on a field, a duplicate column gets created at the right end of the grid so that I have one column with the sort showing and one without. Something like this:

--------------------------------------------------
| DateDue  | PriorityID | DateDue   | PriorityID |
| tblTasks | tblTasks   | tblTasks  | tblTasks   |
|          |            | Ascending | Ascending  |
--------------------------------------------------

The query still works, but it gets really confusing when I'm designing multi-column sorts.

Is this some new behavior in v.2010 (my newest version... yeah, I know, I'm way behind)? Is there a setting somewhere that I can change to make it stop doing this?

[CJ_London]

Depends on the query - show the whole sql

[gr8dane]

SELECT tblTestTasks.Task, tblPriorities.Priority, tblPriorities.PriorityRGB, tblTestTasks.DateDue, tblTestTasks.PriorityID, tblTestTasks.ProjectID, tblTestProjects.PriorityID, tblTestProjects.Project, IIf(IsNull([tblTestTasks].[DateDue]),1,0) AS [Order]
FROM (tblTestTasks LEFT JOIN tblPriorities ON tblTestTasks.PriorityID = tblPriorities.PriorityID) LEFT JOIN tblTestProjects ON tblTestTasks.ProjectID = tblTestProjects.ProjectID
WHERE (((tblTestTasks.DateDue)>#4/12/2024# Or (tblTestTasks.DateDue) Is Null))
ORDER BY IIf(IsNull([tblTestTasks].[DateDue]),1,0), tblTestTasks.DateDue, tblTestTasks.PriorityID;

[Micron]

You already created a calculated field for Order; why not use it?
ORDER BY [Order], tblTestTasks.DateDue, tblTestTasks.PriorityID;

That is probably where your extra query field comes from.

[gr8dane]

First, because the SQL is Access-generated. Secondly, because I read that Access doesn't allow that. I tried it out, though, and it resulted in another duplicate column named Order and when I tried switching to DS view, I got a parameter pop-up for Order.

[Micron]

I took it that [Order] as a calculated field you have created would not conflict with the word "order" in the ORDER BY clause. However, I just reviewed my source for reserved words and see that it is reserved - for Jet, SQL Server and ODBC so maybe I would use something else and see if the prompt goes away. However, upon reviewing I'm not understanding why you have the expression a second time. It looks to me like your result would be
ORDER BY 1 (or zero), tblTestTasks.DateDue, tblTestTasks.PriorityID;
1 or 0 is going to be interpreted as a query field, no?

I'm afraid that your code snippet above isn't really revealing the issue to me. A sort on 2 or more fields should sort by field1, then field2, then field3 and so on, and as long as those fields are in the SELECT statement, they should be visible. There should be no repetition of query fields unless a field is in the SELECT statement more than once.

Maybe a pic of your query design grid would help, if you can show all of it.

[Bulzie]

Not sure why happens, just delete the duplicate and just have the 1 field with the sort on it. If this is a report, can set the sort in the report.

[Welshgasman]

Not sure why happens, just delete the duplicate and just have the 1 field with the sort on it. If this is a report, can set the sort in the report.

In fact Access reports take no notice of any external order in the first place, so if a report it HAS​ to be done in the report.

[gr8dane]

I took it that [Order] as a calculated field you have created would not conflict with the word "order" in the ORDER BY clause. However, I just reviewed my source for reserved words and see that it is reserved - for Jet, SQL Server and ODBC so maybe I would use something else and see if the prompt goes away.

There doesn't seem to be a problem with using the word as an alias. Only with using it later in the SQL.

However, upon reviewing I'm not understanding why you have the expression a second time.

I don't understand it either. It's Access-generated.

It looks to me like your result would be
ORDER BY 1 (or zero), tblTestTasks.DateDue, tblTestTasks.PriorityID;
1 or 0 is going to be interpreted as a query field, no?

This query is the source for a continuous form. The purpose of IIf(IsNull([tblTestTasks].[DateDue]),1,0) is to make records without a due date show up after records with one. They then get sorted by priority.

Maybe a pic of your query design grid would help, if you can show all of it.

I'm attaching pics of the design grid and the resulting datasheet. I took Bulzie's advice and removed the non-sorting columns. That worked (once I remembered to make the remaining ones visible), but I'd still like to know why they got added in the first place.

[CJ_London]

You could just order by

[date due] is not null

[gr8dane]

What would that do? I want the records with a due date to rise to the top, sorted by due date. The rest should be ordered by priority. That's what my method does, but I don't understand what result I would get using your method.

[CJ_London]

Just suggesting a simpler and more efficient method

Booleans are either -1 for true or 0 for false

So sorting ascending, trues will appear before false.

how you sort after that is down to you