Nz is not working in crosstab query

[gormcd]

I have a crosstab query in which I am trying to use the Nz function with no success.

Here's what I have:

Field -- The Value:Nz([WorkStatus])
Table -- BLANK
Total -- Count
Crosstab -- Value

Or... in SQL View:

TRANSFORM Count(Nz([WorkStatus])) AS [The Value]
SELECT qryNewHires.DeptNumber, qryNewHires.JobCode, Count(qryNewHires.WorkStatus) AS [Total Of Count]
FROM qryNewHires
GROUP BY qryNewHires.DeptNumber, qryNewHires.JobCode
PIVOT qryNewHires.WorkStatus In ("F","P","I","N","D"

Thanks for your help!!
gormcd
%-)

 

[bobbster2000]

Hi, make sure you're not doing any of these mistakes

Common Errors with Null
Provided by Allen Browne, abrowne@odyssey.apana.org.au, November 1999.



Here are some common mistakes newbies make with Nulls.


Error 1: Nulls in Criteria
If you enter criteria under a field in a query, it returns only matching records. Nulls are excluded when you enter criteria.

For example, say you have a table of company names and addresses. You want two queries: one that gives you the local companies, and the other that gives you all the rest. In the Criteria row under the City field of the first query, you type:

"Springfield"
and in the second query:

Not "Springfield"
Wrong! Neither query includes the records where City is Null.

Solution
Specify Is Null. For the second query above to meet your design goal of "all the rest", the criteria needs to be:

Is Null Or Not "Springfield"

--------------------------------------------------------------------------------

Error 2: Nulls in expressions
Maths involving a Null usually results in Null. For example, newbies sometimes enter an expression such as this in the ControlSource property of a text box, to display the amount still payable:

=[AmountDue] - [AmountPaid]
The trouble is that if nothing has been paid, AmountPaid is Null, and so this text box displays nothing at all.

Solution
Use the Nz() function to specify a value for Null:

= Nz([AmountDue], 0) - Nz([AmountPaid], 0)

--------------------------------------------------------------------------------

Error 3: Nulls in Foreign Keys
While Access blocks nulls in primary keys, it permits nulls in foreign keys. In most cases, you should explicitly block this possibility to prevent orphaned records.

For a typical Invoice table, the line items of the invoice are stored in an InvoiceDetail table, joined to the Invoice table by an InvoiceID. You create a relationship between Invoice.InvoiceID and InvoiceDetail.InvoiceID, with Referential Integrity enforced. It's not enough!

Unless you set the Required property of the InvoiceID field to Yes in the InvoiceDetail table, Access permits Nulls. Most often this happens when a user begins adding line items to the subform without first creating the invoice itself in the main form. Since these records don't match any record in the main form, these orphaned records are never displayed again. The user is convinced your program lost them, though they are still there in the table.

Solution
Always set the Required property of foreign key fields to Yes in table design view, unless you expressly want Nulls in the foreign key.


--------------------------------------------------------------------------------

Error 4: Nulls and non-Variants
In Visual Basic, the only data type that can contain Null is the Variant. Whenever you assign the value of a field to a non-variant, you must consider the possibility that the field may be null. Can you see what could go wrong with this code in a form's module?

Dim strName as String
Dim lngID As Long
strName = Me.MiddleName
lngID = Me.ClientID
When the MiddleName field contains Null, the attempt to assign the Null to a string generates an error.

Similarly the assignment of the ClientID value to a numeric variable may cause an error. Even if ClientID is the primary key, the code is not safe: the primary key contains Null at a new record.

Solutions
(a) Use a Variant data type if you need to work with nulls.

(b) Use the Nz() function to specify a value to use for Null. For example:

strName = Nz(Me.MiddleName, "&quot
lngID = Nz(Me.ClientID, 0)

--------------------------------------------------------------------------------

Error 5: Comparing something to Null
The expression:

If [Surname] = Null Then
is a nonsense that will never be True. Even if the surname is Null, VBA thinks you asked:

Does Unknown equal Unknown?
and always responds "How do I know whether your unknowns are equal?" This is Null propagation again: the result is neither True nor False, but Null.

Solution
Use the IsNull() function:

If IsNull([Surname]) Then

--------------------------------------------------------------------------------

Error 6: Forgetting Null is neither True nor False.
Do these two constructs do the same job?

(a) If [Surname] = "Smith" Then
MsgBox "It's a Smith"
Else
MsgBox "It's not a Smith"
End If

(b) If [Surname] <> &quot;Smith&quot; Then
MsgBox &quot;It's not a Smith&quot;
Else
MsgBox &quot;It's a Smith&quot;
End If
When the Surname is Null, these 2 pieces of code contradict each other. In both cases, the If fails, so the Else executes, resulting in contradictory messages.

Solutions
(a) Handle all three outcomes of a comparison - True, False, and Null:

If [Surname] = &quot;Smith&quot; Then
MsgBox &quot;It's a Smith&quot;
ElseIf [Surname] <> &quot;Smith&quot; Then
MsgBox &quot;It's not a Smith&quot;
Else
MsgBox &quot;We don't know if it's a Smith&quot;
End If
(b) In some cases, the Nz() function lets you to handle two cases together. For example, to treat a Null and a zero-length string in the same way:

If Len(Nz([Surname],&quot;&quot) = 0 Then