Book

Suggestions


Enter your email address:

Delivered by FeedBurner



Use your pdf converter to make your pdf files easy! You can now buy software that makes converting pdf to doc possible! Did you know you can even convert pdf to word?
Home Page

Bloglines

1906
CelebrateStadium
2006


OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal












Subscribe here
Add to 

My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com



  Thursday, December 11, 2008 – Permalink –

Alphabetize by One Field or the Other

If one is missing, use the other


Let's say you have a database that has the company name and a contacts name.

In some cases the CompanyName field is empty. If that happens, you want to continue the alpha sort using the contact's LastName.

To do this, you need to create an extra query field to provide the sort, using the NZ() function to replace the contents of one field for null values in another.

(Nz(variant, [valueifnull])

  1. Select the Queries, and then click "Create query in Design view".
  2. Choose the table you want to sort, click Add.
  3. Click Close.
  4. Drag down all the fields you want to display in your form, including the two separate fields you want to alphabetize.
  5. Insert a new column on the left side of the QBE grid.
  6. In the Field cell, enter the expression
    NZ([CompanyName],[LastName])
  7. Select Ascending for the Sort option.


When you run the query, if CompanyName is null (empty — no entry), the NZ() function uses the contents in LastName instead.

Here's another way to do it:







See all Topics

Labels: , ,


<Doug Klippert@ 3:47 AM

Comments: Post a Comment