Query Ascending Question

[Bob]

In a Ascending order on a query how can I get the blanks to come last, now
its ordering blanks first then a,b,c,...Thanks for your help...Bob

 

[Brendan Reynolds]

SELECT SomeTable.*
FROM SomeTable
ORDER BY [SomeColumn] Is Null, SomeColumn;

Where 'SomeTable is the name of your table and 'SomeColumn' is the name of
the column on which you want to sort.

 

[Douglas J. Steele]

Or if it really is blanks, and not nulls,

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), SomeColumn;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

SELECT SomeTable.*
FROM SomeTable
ORDER BY [SomeColumn] Is Null, SomeColumn;

Where 'SomeTable is the name of your table and 'SomeColumn' is the name of
the column on which you want to sort.

--
Brendan Reynolds
Access MVP

In a Ascending order on a query how can I get the blanks to come last,
now its ordering blanks first then a,b,c,...Thanks for your help...Bob

 

[Brendan Reynolds]

Yes I meant to say something about what exactly is meant by 'blank' in this
context, but forgot. Thanks Doug.

Bob, the issue Doug and I are discussing here is whether those 'blanks' are
null values, empty strings, strings of spaces, or some combination of the
above.

--
Brendan Reynolds
Access MVP

Or if it really is blanks, and not nulls,

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), SomeColumn;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

SELECT SomeTable.*
FROM SomeTable
ORDER BY [SomeColumn] Is Null, SomeColumn;

Where 'SomeTable is the name of your table and 'SomeColumn' is the name
of the column on which you want to sort.

--
Brendan Reynolds
Access MVP

In a Ascending order on a query how can I get the blanks to come last,
now its ordering blanks first then a,b,c,...Thanks for your help...Bob

 

[Bob]

Thanks Douglas I added a field at the start and it seem to do the trick
Expr1: [Cb7] Is Not Null, it gave me a -1 on info in Cb7 and then I did a
ascending on Expr1 and on Cb7, Bingo
Have another problem, I have another query that I want to sort by numbers I
have a Value Field 1;2;3;4;
but on my sort I am getting
1
12
2
3
33
4
Any Help Thanx Bob

Or if it really is blanks, and not nulls,

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), SomeColumn;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

SELECT SomeTable.*
FROM SomeTable
ORDER BY [SomeColumn] Is Null, SomeColumn;

Where 'SomeTable is the name of your table and 'SomeColumn' is the name
of the column on which you want to sort.

--
Brendan Reynolds
Access MVP

In a Ascending order on a query how can I get the blanks to come last,
now its ordering blanks first then a,b,c,...Thanks for your help...Bob

 

[Ron2006]

relative to the sort in which the records are coming out in the
strange order.

1
12
2
3
33
4

That is because the field is defined as a text field and not a numeric
field. You will have to either change it to numeric or load the info
into yet another field that is numeric. or sort on a dynamically
created field that converts the above field to numeric.

Ron

 

[Douglas J. Steele]

The implication is that the field is Text, not Numeric.

Try

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), Val(SomeColumn)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas I added a field at the start and it seem to do the trick
Expr1: [Cb7] Is Not Null, it gave me a -1 on info in Cb7 and then I did a
ascending on Expr1 and on Cb7, Bingo
Have another problem, I have another query that I want to sort by numbers
I have a Value Field 1;2;3;4;
but on my sort I am getting
1
12
2
3
33
4
Any Help Thanx Bob

Or if it really is blanks, and not nulls,

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), SomeColumn;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

SELECT SomeTable.*
FROM SomeTable
ORDER BY [SomeColumn] Is Null, SomeColumn;

Where 'SomeTable is the name of your table and 'SomeColumn' is the name
of the column on which you want to sort.

--
Brendan Reynolds
Access MVP




In a Ascending order on a query how can I get the blanks to come last,
now its ordering blanks first then a,b,c,...Thanks for your help...Bob

 

[Bob]

Douglas where do I put this code....Thanx Bob

The implication is that the field is Text, not Numeric.

Try

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), Val(SomeColumn)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas I added a field at the start and it seem to do the trick
Expr1: [Cb7] Is Not Null, it gave me a -1 on info in Cb7 and then I did a
ascending on Expr1 and on Cb7, Bingo
Have another problem, I have another query that I want to sort by numbers
I have a Value Field 1;2;3;4;
but on my sort I am getting
1
12
2
3
33
4
Any Help Thanx Bob

Or if it really is blanks, and not nulls,

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), SomeColumn;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


SELECT SomeTable.*
FROM SomeTable
ORDER BY [SomeColumn] Is Null, SomeColumn;

Where 'SomeTable is the name of your table and 'SomeColumn' is the name
of the column on which you want to sort.

--
Brendan Reynolds
Access MVP




In a Ascending order on a query how can I get the blanks to come last,
now its ordering blanks first then a,b,c,...Thanks for your help...Bob

 

[Bob]

Thanks I just changed table field to numbers and all fine..Thanks Bob

Douglas where do I put this code....Thanx Bob

The implication is that the field is Text, not Numeric.

Try

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), Val(SomeColumn)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas I added a field at the start and it seem to do the trick
Expr1: [Cb7] Is Not Null, it gave me a -1 on info in Cb7 and then I did
a ascending on Expr1 and on Cb7, Bingo
Have another problem, I have another query that I want to sort by
numbers I have a Value Field 1;2;3;4;
but on my sort I am getting
1
12
2
3
33
4
Any Help Thanx Bob

Or if it really is blanks, and not nulls,

SELECT SomeTable.*
FROM SomeTable
ORDER BY IIf(Trim(Len([SomeColumn] & " ")) = 0, 1, 0), SomeColumn;

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message SELECT SomeTable.*
FROM SomeTable
ORDER BY [SomeColumn] Is Null, SomeColumn;

Where 'SomeTable is the name of your table and 'SomeColumn' is the
name of the column on which you want to sort.

--
Brendan Reynolds
Access MVP




In a Ascending order on a query how can I get the blanks to come
last, now its ordering blanks first then a,b,c,...Thanks for your
help...Bob