Sorting Question

  • Thread starter Thread starter SamDev
  • Start date Start date
S

SamDev

I have a report that includes the fields Company Name, First Name, Last
Name. I have it sorted first by Company Name, then by Last Name, then by
First Name (in Ascending order). For records that do not Company Name that
field is blank and therefore all the records without a Company Name are at
the beginning of the sort - I understand that is the way Access sorts but is
there a way around this so that I can still sort in the order I have set but
have the records without a Company name go after the ones that do?

Much thanks!
 
Perfect!!! Much thanks!!!


Duane Hookom said:
Set the sorting and grouping level expression to:
=Nz([Company Name],"zzzz")

--
Duane Hookom
MS Access MVP
--

SamDev said:
I have a report that includes the fields Company Name, First Name, Last
Name. I have it sorted first by Company Name, then by Last Name, then by
First Name (in Ascending order). For records that do not Company Name that
field is blank and therefore all the records without a Company Name are at
the beginning of the sort - I understand that is the way Access sorts but
is there a way around this so that I can still sort in the order I have
set but have the records without a Company name go after the ones that do?

Much thanks!
Click to expand...
Click to expand...
 
Oops - another question - how would I do the same in a Query? I tried using
the same expression
=Nz([Company Name],"zzzz")

in a query criteria and it doesn't include the records that do not have a
Company Name...

Duane Hookom said:
Set the sorting and grouping level expression to:
=Nz([Company Name],"zzzz")

--
Duane Hookom
MS Access MVP
--

SamDev said:
I have a report that includes the fields Company Name, First Name, Last
Name. I have it sorted first by Company Name, then by Last Name, then by
First Name (in Ascending order). For records that do not Company Name that
field is blank and therefore all the records without a Company Name are at
the beginning of the sort - I understand that is the way Access sorts but
is there a way around this so that I can still sort in the order I have
set but have the records without a Company name go after the ones that do?

Much thanks!
Click to expand...
Click to expand...
 
You don't do it in the criteria of the query.

In a new column in your query put the following...

CompanySort: Nz([Company Name],"zzzz")

Then set it to be your sort (ascending or descending)

Then uncheck the box at the bottom of the design grid so this new field will
not show, but will still be used for sorting.

--
Rick B



SamDev said:
Oops - another question - how would I do the same in a Query? I tried
using the same expression
=Nz([Company Name],"zzzz")

in a query criteria and it doesn't include the records that do not have a
Company Name...

Duane Hookom said:
Set the sorting and grouping level expression to:
=Nz([Company Name],"zzzz")

--
Duane Hookom
MS Access MVP
--

SamDev said:
I have a report that includes the fields Company Name, First Name, Last
Name. I have it sorted first by Company Name, then by Last Name, then by
First Name (in Ascending order). For records that do not Company Name
that field is blank and therefore all the records without a Company Name
are at the beginning of the sort - I understand that is the way Access
sorts but is there a way around this so that I can still sort in the
order I have set but have the records without a Company name go after the
ones that do?

Much thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thank YOU, thank YOU! Works perfect as long as the new column is the left of
the other columns!!!



Rick B said:
You don't do it in the criteria of the query.

In a new column in your query put the following...

CompanySort: Nz([Company Name],"zzzz")

Then set it to be your sort (ascending or descending)

Then uncheck the box at the bottom of the design grid so this new field
will not show, but will still be used for sorting.

--
Rick B



SamDev said:
Oops - another question - how would I do the same in a Query? I tried
using the same expression
=Nz([Company Name],"zzzz")

in a query criteria and it doesn't include the records that do not have a
Company Name...

Duane Hookom said:
Set the sorting and grouping level expression to:
=Nz([Company Name],"zzzz")

--
Duane Hookom
MS Access MVP
--

I have a report that includes the fields Company Name, First Name, Last
Name. I have it sorted first by Company Name, then by Last Name, then by
First Name (in Ascending order). For records that do not Company Name
that field is blank and therefore all the records without a Company Name
are at the beginning of the sort - I understand that is the way Access
sorts but is there a way around this so that I can still sort in the
order I have set but have the records without a Company name go after
the ones that do?

Much thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top