P
PPierce
I need to sort null records to the bottom of my query.
Help Please!!
Help Please!!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
W
Wayne Morgan
Insert a calculated field and sort on it instead of the field you are
sorting on. Remove the check box from the Show row since you don't need it
in the output of the query. In the calculation, replace the Null with a
value that doesn't and won't exist in the field and will be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
sorting on. Remove the check box from the Show row since you don't need it
in the output of the query. In the calculation, replace the Null with a
value that doesn't and won't exist in the field and will be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
P
PPierce
You mean to say that there is no way to write in the sql to
tell it to place the nulls at the bottom?
tell it to place the nulls at the bottom?
-----Original Message-----
Insert a calculated field and sort on it instead of the field you are
sorting on. Remove the check box from the Show row since you don't need it
in the output of the query. In the calculation, replace the Null with a
value that doesn't and won't exist in the field and will be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
--
Wayne Morgan
Microsoft Access MVP
I need to sort null records to the bottom of my query.
Help Please!!
.
W
Wayne Morgan
I am not aware of a SQL command in Access that will do that unless you want
to sort descending; however, this will reverse the entire list, not just put
the Nulls as the bottom. I'm not familiar enough with SQL Server, if that's
what you're using, to say whether or not there is one there.
to sort descending; however, this will reverse the entire list, not just put
the Nulls as the bottom. I'm not familiar enough with SQL Server, if that's
what you're using, to say whether or not there is one there.
G
Gary Walter
Hi PPierce,
If you were sorting on text field "Field1,"
then Wayne HAS shown you one way to
"write in the sql..."
ORDER BY Nz([Field1], "zzzzzzzzzzzzzz"), ....
Other ways could be to sort on a condition:
ORDER BY ([Field1] IS NULL) DESC, ...
or
ORDER BY (Len([Field1] & "") > 0), ...
You can make a minor change to the last one
to place BOTH nulls and zero-length strings "at
the bottom":
ORDER BY (Len(Trim([Field1] & "")) > 0), ...
These depend on the condition in Access
returning -1 when true and 0 when false.
DESC says sort the 0's (falses) before the
-1's (trues).
The "implied default ascending" says sort -1's (trues)
before 0's (falses).
If you were sorting on text field "Field1,"
then Wayne HAS shown you one way to
"write in the sql..."
ORDER BY Nz([Field1], "zzzzzzzzzzzzzz"), ....
Other ways could be to sort on a condition:
ORDER BY ([Field1] IS NULL) DESC, ...
or
ORDER BY (Len([Field1] & "") > 0), ...
You can make a minor change to the last one
to place BOTH nulls and zero-length strings "at
the bottom":
ORDER BY (Len(Trim([Field1] & "")) > 0), ...
These depend on the condition in Access
returning -1 when true and 0 when false.
DESC says sort the 0's (falses) before the
-1's (trues).
The "implied default ascending" says sort -1's (trues)
before 0's (falses).
PPierce said:You mean to say that there is no way to write in the sql to
tell it to place the nulls at the bottom?
-----Original Message-----
Insert a calculated field and sort on it instead of the field you are
sorting on. Remove the check box from the Show row since you don't need it
in the output of the query. In the calculation, replace the Null with a
value that doesn't and won't exist in the field and will be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
--
Wayne Morgan
Microsoft Access MVP
I need to sort null records to the bottom of my query.
Help Please!!
.
G
Guest
OK this is getting me really close to what I need but after
it sorts the nulls to the bottom I need the rest of the
same column to be sorted ascending and I can't seem to
figure out just how to do that. Please help and sorry to
seem so dumb but it just doesn't work for me.
it sorts the nulls to the bottom I need the rest of the
same column to be sorted ascending and I can't seem to
figure out just how to do that. Please help and sorry to
seem so dumb but it just doesn't work for me.
-----Original Message-----
Hi PPierce,
If you were sorting on text field "Field1,"
then Wayne HAS shown you one way to
"write in the sql..."
ORDER BY Nz([Field1], "zzzzzzzzzzzzzz"), ....
Other ways could be to sort on a condition:
ORDER BY ([Field1] IS NULL) DESC, ...
or
ORDER BY (Len([Field1] & "") > 0), ...
You can make a minor change to the last one
to place BOTH nulls and zero-length strings "at
the bottom":
ORDER BY (Len(Trim([Field1] & "")) > 0), ...
These depend on the condition in Access
returning -1 when true and 0 when false.
DESC says sort the 0's (falses) before the
-1's (trues).
The "implied default ascending" says sort -1's (trues)
before 0's (falses).
PPierce said:You mean to say that there is no way to write in the sql to
tell it to place the nulls at the bottom?
-----Original Message-----
Insert a calculated field and sort on it instead of the field you are
sorting on. Remove the check box from the Show row since you don't need it
in the output of the query. In the calculation, replace the Null with a
value that doesn't and won't exist in the field and will be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
--
Wayne Morgan
Microsoft Access MVP
I need to sort null records to the bottom of my query.
Help Please!!
.
.
W
Wayne Morgan
Gary had a very good idea. It will require sorting on 2 fields. Sort for the
Null first and then on the field you are trying to sort.
Example:
SELECT Table1.*
FROM Table1
ORDER BY IsNull([Table1].[Field1]) DESC , Table1.Field1;
--
Wayne Morgan
Microsoft Access MVP
Null first and then on the field you are trying to sort.
Example:
SELECT Table1.*
FROM Table1
ORDER BY IsNull([Table1].[Field1]) DESC , Table1.Field1;
--
Wayne Morgan
Microsoft Access MVP
OK this is getting me really close to what I need but after
it sorts the nulls to the bottom I need the rest of the
same column to be sorted ascending and I can't seem to
figure out just how to do that. Please help and sorry to
seem so dumb but it just doesn't work for me.
-----Original Message-----
Hi PPierce,
If you were sorting on text field "Field1,"
then Wayne HAS shown you one way to
"write in the sql..."
ORDER BY Nz([Field1], "zzzzzzzzzzzzzz"), ....
Other ways could be to sort on a condition:
ORDER BY ([Field1] IS NULL) DESC, ...
or
ORDER BY (Len([Field1] & "") > 0), ...
You can make a minor change to the last one
to place BOTH nulls and zero-length strings "at
the bottom":
ORDER BY (Len(Trim([Field1] & "")) > 0), ...
These depend on the condition in Access
returning -1 when true and 0 when false.
DESC says sort the 0's (falses) before the
-1's (trues).
The "implied default ascending" says sort -1's (trues)
before 0's (falses).
PPierce said:You mean to say that there is no way to write in the sql to
tell it to place the nulls at the bottom?
-----Original Message-----
Insert a calculated field and sort on it instead of the
field you are
sorting on. Remove the check box from the Show row since
you don't need it
in the output of the query. In the calculation, replace
the Null with a
value that doesn't and won't exist in the field and will
be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
--
Wayne Morgan
Microsoft Access MVP
message
I need to sort null records to the bottom of my query.
Help Please!!
.
.
P
ppierce
wayne, this did not work for me. it still sorts the nulls
at the top...any other suggestions???
at the top...any other suggestions???
-----Original Message-----
Gary had a very good idea. It will require sorting on 2 fields. Sort for the
Null first and then on the field you are trying to sort.
Example:
SELECT Table1.*
FROM Table1
ORDER BY IsNull([Table1].[Field1]) DESC , Table1.Field1;
--
Wayne Morgan
Microsoft Access MVP
OK this is getting me really close to what I need but after
it sorts the nulls to the bottom I need the rest of the
same column to be sorted ascending and I can't seem to
figure out just how to do that. Please help and sorry to
seem so dumb but it just doesn't work for me.
-----Original Message-----
Hi PPierce,
If you were sorting on text field "Field1,"
then Wayne HAS shown you one way to
"write in the sql..."
ORDER BY Nz([Field1], "zzzzzzzzzzzzzz"), ....
Other ways could be to sort on a condition:
ORDER BY ([Field1] IS NULL) DESC, ...
or
ORDER BY (Len([Field1] & "") > 0), ...
You can make a minor change to the last one
to place BOTH nulls and zero-length strings "at
the bottom":
ORDER BY (Len(Trim([Field1] & "")) > 0), ...
These depend on the condition in Access
returning -1 when true and 0 when false.
DESC says sort the 0's (falses) before the
-1's (trues).
The "implied default ascending" says sort -1's (trues)
before 0's (falses).
You mean to say that there is no way to write in the sql to
tell it to place the nulls at the bottom?
-----Original Message-----
Insert a calculated field and sort on it instead of the
field you are
sorting on. Remove the check box from the Show row since
you don't need it
in the output of the query. In the calculation, replace
the Null with a
value that doesn't and won't exist in the field and will
be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
--
Wayne Morgan
Microsoft Access MVP
message
I need to sort null records to the bottom of my query.
Help Please!!
.
.
.
W
Wayne Morgan
Go to your query in SQL view and copy and paste it into a message please.
--
Wayne Morgan
MS Access MVP
--
Wayne Morgan
MS Access MVP
ppierce said:wayne, this did not work for me. it still sorts the nulls
at the top...any other suggestions???
-----Original Message-----
Gary had a very good idea. It will require sorting on 2 fields. Sort for the
Null first and then on the field you are trying to sort.
Example:
SELECT Table1.*
FROM Table1
ORDER BY IsNull([Table1].[Field1]) DESC , Table1.Field1;
--
Wayne Morgan
Microsoft Access MVP
OK this is getting me really close to what I need but after
it sorts the nulls to the bottom I need the rest of the
same column to be sorted ascending and I can't seem to
figure out just how to do that. Please help and sorry to
seem so dumb but it just doesn't work for me.
-----Original Message-----
Hi PPierce,
If you were sorting on text field "Field1,"
then Wayne HAS shown you one way to
"write in the sql..."
ORDER BY Nz([Field1], "zzzzzzzzzzzzzz"), ....
Other ways could be to sort on a condition:
ORDER BY ([Field1] IS NULL) DESC, ...
or
ORDER BY (Len([Field1] & "") > 0), ...
You can make a minor change to the last one
to place BOTH nulls and zero-length strings "at
the bottom":
ORDER BY (Len(Trim([Field1] & "")) > 0), ...
These depend on the condition in Access
returning -1 when true and 0 when false.
DESC says sort the 0's (falses) before the
-1's (trues).
The "implied default ascending" says sort -1's (trues)
before 0's (falses).
You mean to say that there is no way to write in the sql to
tell it to place the nulls at the bottom?
-----Original Message-----
Insert a calculated field and sort on it instead of the
field you are
sorting on. Remove the check box from the Show row since
you don't need it
in the output of the query. In the calculation, replace
the Null with a
value that doesn't and won't exist in the field and will
be sorted after
everything else.
Example:
SortOnMe: Nz([Table1].[Field1], "zzzzzzzzzzzzzz")
--
Wayne Morgan
Microsoft Access MVP
message
I need to sort null records to the bottom of my query.
Help Please!!
.
.
.