MS Access 2007 Query/Report Sort question

DESIRED RESULT: I need a report that sorts my data by COMPANY name in ascending order. And then, by LASTNAME (within a Company if there are last names). Here's an example of what I want:

COMPANY                      LASTNAME
AllStateInsurance          
Bank of America             Alard
                                        Bernard
                                        Fitzgerald
                                        Hinds
                                        Young
Brown Construction        Brown
                                        Defazio
                                        McCapes
C&M Consulting
Citibank
...
Zim Industries
                                        Anderson
                                        Bennet
                                        Bentley
                                        ....
                                        Zimmerman
                                       
PROBLEM: Records where the Company name is "blank/empty/or Null" go to the top of my report and records are then sorted by Lastname.
So it looks like this:
COMPANY                    LASTNAME
                                     Anderson
                                     Bennet
                                     Bentley
                                      ....
                                      Zimmerman
AllStateInsurance          
Bank of America             Alard
                                        Bernard


I don't want records without a Company name to go to the beginning of the report. I think I need help with creating a simple expression to force the sort I want but I don't know how to do it.  Thanks in advance for your help!
MostHumbleNetmanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
hnasrConnect With a Mentor Commented:
Sorting is done in report, not query
In the report design, select Sorting and Grouping, and sort on an expression: nz(Company,"z")

Thanks boag2000. Sorting in report, and using the z will put the company last without displaying it.
0
 
hnasrCommented:
Did you try the sort on expression:

Nz(Company,"z")
0
 
Jeffrey CoachmanMIS LiasonCommented:
I think hnasr has the solution you want.
One enhancement might be to hide the "Z" in the report.

But as hnasr suggested this first, I will give him the honor of embellishing on this, if he so chooses...
;-)


Just curious...
Why would a Company "Not" have a name?
How is this stored in the Company table?, with just the CompanyID?
In the US, in order to be a business "Entity" you must have a name, even if it is temporary.

JeffCoachman



0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
MostHumbleNetmanAuthor Commented:
This particular database is used for newsletters and fundraising letters. It contains under 5,000 records. The filelds for Company, MailAs, Salutation, JobTitle, LastName, FirstName, Address1, Address2, City, State, Zip, Phone, etc. are all in the same table. There are a couple of other tables such as contributions that are in other tables but the main data records are all in the table called tblMailList. So, to answer your question, it contains business as well as individual's information.

0
 
MostHumbleNetmanAuthor Commented:
I apologize for asking what's probably a dumb question... but, do I go to my query for this report and put the info ... Nz(Company,"z") exactly as you've given it to me?  Here's are screen shots of my query. Perhaps this will help you to explain how I should set it up? Thanks again for your help.
ExistingQuery-ScreenShot.docx
0
 
Jeffrey CoachmanMIS LiasonCommented:
<it contains business as well as individual's information.>
Ahhh. I see

Thanks for the clarification.

hnasr should be along shortly to help you with the NZ()

;-)

Jeff
0
 
hnasrCommented:
If you have a company name za..., then use zzzzz....!
0
 
Jeffrey CoachmanMIS LiasonCommented:
<Sorting in report, and using the z will put the company last without displaying it.>
Ah, OK, I thought that the NZ() was the field val.
But yes, if the "sort expression" is used, the Z should not display

Sorry about that, ...quick reading...

;-)

Jeff
0
 
MostHumbleNetmanAuthor Commented:
I've uploaded a screen shot of my report in Design View. Where do I insert the expression you gave me?
nz(Company,"z")

CompanyReport-ScreenShot-DesignV.docx
0
 
MostHumbleNetmanAuthor Commented:
I finally figured it out!
Here's what I did:
1. Open the Report in Design View (or open the report, right-click and select Design View)
2. Click on "Group and Sort" under the Design Tab
3. In the Group, Sort, And Total section (at the bottom of the Report, which is now in Design View), Click on the Arrow to the right of SORT BY and Select Sort by Expression (it's at the bottom of the list).
4. This will bring up the expression builder. I pasted the expression you provided me into the builder entry field nz(Company,"z") and hit ok, saved my report and hit View. It worked!

P.S. when I reopened the expression I'd pasted, I found that the expression builder automatically changed it to: =nz([Company],"z")    I later added the extra z recommended so that my final expression was: nz([Company],"zz")

 
0
 
MostHumbleNetmanAuthor Commented:
The only reason it wasn't easy to follow was because I am new to using expressions. But, after some trial and error I was able to correctly apply the answer to the problem and it worked.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
Soon you won't need us at all...

;-)
0
 
MostHumbleNetmanAuthor Commented:
ha! I doubt that...but appreciate all your help. Thanks again!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.