Digging in to DataMatch; Using the SQL NULLIF function

I've gotten through InstallShield to the point where I can go back to DataMatch itself. At some point I'd like to update the projects page on my site, but for now I'll post here.

DataMatch is basically a Data Entry application so that photographers who shoot large volumes of pictures can rapidly enter data as to what packages or print sizes they would like for each image.

We had 3 different job types, and fields unique to those job types that could be viewed/edited while entering data. So I created tables to store which fields were going to be visible on the form.

However, 1 set of selections for each job type wouldn't work when you had multiple users at different stations and different jobs requiring different sets of fields available.

So I am working to make it so that each job will have the required fields associated with it.

To speed up data entry, we included an option to import data from text files for use in the program. Another feature I am trying to add is the ability for the program to see which fields have data and auto select them for that job, unselecting others, so that unnecessary fields don't clutter the screen. (Users can of course reselect them if need be.)

This is where I have been digging into SQL code. I figured I could do a stored procedure that would look at a table like this:
Field1   Field2   Field3
0001     (null)   PieceOfData
0002     (null)   PieceOfData
0003     (null)
0004     Something PieceOfData
0005     (null)
0006     (null) PieceOfData

And return something like this:
Field1   Field2   Field3
6        1        4

I thought at first I could do this:
SELECT
 Field1 = COUNT(Field1),
 Field2 = COUNT(Field2),
 Field3 = COUNT(Field3)
FROM
 Table

But that would also count zero length strings so, Field3 would return 6 instead of 4.

Then I tried SQL code like this:
SELECT
 Field1 = (SELECT count(Field1) FROM Table WHERE Field1 <> '' AND RecID IS NOT Null),
 Field2 = (SELECT count(Field2) FROM Table WHERE Field2 <> '' AND Field2 IS NOT Null),
 Field3 = (SELECT count(Field3) FROM Table WHERE Field3 <> '' AND Field3 IS NOT Null)
FROM Table

While this gave me the results I wanted, it also cause 4 passes over the table. (Or more if you had more fields.) Well... Maurits on Channel 9 offered this more elegant solution:
SELECT
 Field1 = COUNT(NULLIF(Field1, '')), -- string field
 Field2 = COUNT(Field2), -- integer field
 Field3 = COUNT(NULLIF(Field3, 0)) -- bit field... only count 1s
FROM
 Table

The NULLIF function checks the field (in parameter 1) for the value you specify in parameter 2. Changing the Field3 statement to check for '' I was able to get the results I wanted. ('' is for zero length strings. I have no bit fields in this table.)

This saves typing and time because it only makes one pass over the table.