sort null then by

[Big X]

Hello is there a way to sort a column by null or not null then by a second item.
Basically I have data for bar coding letters and 92% of the records have that data. So I want to to sort it by the bar codes then by state. Problem being barcode is a number and it does not work.sorting it that way. The null not null is the only way and I really just want to know if there is a SQL query that can do that because it will make my job a lot easier.

[Stewart Ross]

Hi. In a normal field sort all nulls are grouped together before any other actual values. If you need to sort in some form of custom order you can add a calculated field to your query which has an IIF like this:

BarcodeNull: IIF(IsNull([yourbarcodefield]), 1, 0)

The SQL for this is

SELECT ... IIF(IsNull([yourbarcodefield]), 1, 0) AS BarcodeNull, ...

All null barcodes will then be flagged with a 1 in this calculated field, non-nulls with 0.

-Stewart

[Big X]

The flag is working. But in the same query can I then go
order by BarcodeNull, State

It does not seem to work and asks me for a value for barcodenull. I guess its trying to order by a field that hasn't been created yet.

[Big X]

at the moment I got it working by first creating a new table with the null values then running another query to do the sorting. Would like to do it in one query if possible but if not no worries.

[Stewart Ross]

Hi. In the Access query editor you can set the Order property of your calculated field to Ascending or Descending as appropriate.

When you assign a name to a calculated field the name is not available to other parts of the same query or SQL statement - as the new field really does not exist until after the query is run - hence the failure you experienced. Does not stop you ordering the field, though, and if you check out the SQL which Access itself produces you will find that the calculated field is in effect repeated for the ordering:

... ORDER BY ..., IIF(ISNull([Yourbarcodefield]), 1, 0), ...

-Stewart

[NeoPa]

Without intending to contradict Stewart, I would just clarify that statement somewhat.

As I understand it, aliased fields (where a name is given to a result after the AS) are generally available within that set of SQL, but with the proviso that they are not available to clauses which are logically executed before the named result is worked out. Of course, this order is not published and may vary between versions of SQL.

I know that other SELECT results generally CAN access an aliased result by name. Generally WHERE & ORDER BY clauses are unable to.

To get past this, it is usual simply to repeat the formula to the left of the AS in place of using the name. As they are equivalent and SQL can always optimise this down anyway, this is pretty well exactly the same (though possibly less maintainable).

[Stewart Ross]

Thanks for your clarification, NeoPa. I have myself used alias names elsewhere in Access queries with varying degrees of success. In one case a query in which a total was aliased and referred to by another calculated field worked well - until a new column was added, when the alias name suddenly became unrecognised. In other cases it simply hasn't been accepted at all (within the SELECT part). Hence why I simplified my response down to the safe option of treating the alias name as unavailable until the query is run. I did not know why Access should behave like that, and your explanation makes it much clearer!

-Stewart

[NeoPa]

No worries Stewart.

On the brink of my mind is some other scenario to do with fields RELATED ...

STOP PRESS :

The other scenario I was trying hard to remember is the situation where a field which is involved in a WHERE clause, but also is displayed in the SELECT clause, refers to another aliased field from the SELECT clause.

This will fail to resolve as the WHERE clause needs to be processed first and the aliased field is not yet available.

Actually, this can be extrapolated from the earlier point when thinking logically, but I mention it because it's easy to miss the reason and see it as one SELECT field being unable to reference another which is aliased. I know I did for a while, until I worked out what must have been going on.