Null values in Crosstab

[swift]

Can anybody help with this please? I need to generate zero/null values in a crosstab query where the query is not counting any data. I know about the nz function but I'm not sure where it goes in the query - I've searched the forum and found a few threads but they have not really helped.
Its counting numbers of DPCodes against CompClassTag, if that helps.
Thanks in advance

swifty

TRANSFORM Count(A1RMMSQRY.DPCode) AS CountOfDPCode
SELECT A1RMMSQRY.CompClassTag
FROM A1RMMSQRY
WHERE (((A1RMMSQRY.SchemeID)=[Forms]![VM_A1_RMMS_Form]![SchemeID]))
GROUP BY A1RMMSQRY.CompClassTag, A1RMMSQRY.SchemeID
PIVOT A1RMMSQRY.DPCode In ("Cat 1","Safe+Cat 1","IMM","Cat 2.1","Cat 2.2","Cat 2.3","Safe+Cat 2.1","Near EOL","Satis","None");

 

[DCrake]

try

TRANSFORM Count([B]Nz([/B]A1RMMSQRY.DPCode[B],0)[/B]) AS CountOfDPCode

 

[swift]

Tried that DCrake, did not work unfortunately. Could it be anything to do with the field type of DPCode in my table? - I've just checked and its currently a text field. I'm going to copy the table etc and change the field a few times and see what happens.

Have you any other ideas? Cheers for the help so far!

 

[vbaInet]

Put the Nz outside the Count()

 

[MSAccessRookie]

try

TRANSFORM Count([B]Nz([/B]A1RMMSQRY.DPCode[B],[COLOR=red]0[/COLOR])[/B]) AS CountOfDPCode

Tried that DCrake, did not work unfortunately. Could it be anything to do with the field type of DPCode in my table? - I've just checked and its currently a text field. I'm going to copy the table etc and change the field a few times and see what happens.

Have you any other ideas? Cheers for the help so far!

The provided response seems to assume that A1RMMSQRY.DPCode is a Numeric type, since it substitutes a Number (0).

If A1RMMSQRY.DPCode is not a Numeric type, then you would need to substitute a properly formatted parameter and see what happens. (Ex: for a string you might use ("0")).

 

[vbaInet]

I think what the OP is trying to do is substitute Null values with "0". I could be missing the point though

 

[boblarson]

What I normally do is to use a query first to coerce 0's if a field is null and then use that query as the source of the crosstab instead of trying to do it all at the same time.

 

[swift]

I think what the OP is trying to do is substitute Null values with "0". I could be missing the point though

yep, thats what I'm trying to do. Sorry if there's any confusion, I'm learning all the time at Access!

Following your suggestion, I've re-arranged the placement of the Nz function to no avail - I'm not all that sure that its in the right place!

TRANSFORM Nz (Count(A1RMMSQRY.DPCode,0)) AS CountOfDPCode
SELECT A1RMMSQRY.CompClassTag
FROM A1RMMSQRY
WHERE (((A1RMMSQRY.SchemeID)=[Forms]![VM_A1_RMMS_Form]![SchemeID]))
GROUP BY A1RMMSQRY.CompClassTag, A1RMMSQRY.SchemeID
PIVOT A1RMMSQRY.DPCode In ("Cat 1","Safe+Cat 1","IMM","Cat 2.1","Cat 2.2","Cat 2.3","Safe+Cat2.1","Near EOL","Satis","None");

 

[boblarson]

Try making a query that replaces the nulls with zeroes and save it. Then use that as the basis for your crosstab.

 

[vbaInet]

Wrong order. Here it is:

Nz(Count(A1RMMSQRY.DPCode), 0)

Hmm... not sure though Bob. Would it not include the zeros in the Count?

 

[boblarson]

Wrong order. Here it is:

Nz(Count(A1RMMSQRY.DPCode), 0)

Hmm... not sure though Bob. Would it not include the zeros in the Count?

If a count of non null's is necessary then it should be done not using NZ anyway as that would do the same thing as what I'm saying to do. If that is the case then NZ is not the right thing anyway and a different method using an IIF would be the way to go.

 

[swift]

Wrong order. Here it is:

Nz(Count(A1RMMSQRY.DPCode), 0)

Hmm... not sure though Bob. Would it not include the zeros in the Count?

vbaInet - you solved it! thanks so much for your help - have some rep

I've just started reading the dummies guide to VBA for Access 2007, hopefully that will help me along the way. Thanks to Bob for input as well, this forum is really helpful for me!

swift

 

[vbaInet]

Twas all a combined effort. Glad we could all help.

 

[DCrake]

As an afterthought you have forced the column headings including the final one the said "None". Therefore if your Nz() read

Count(Nz(A1RMMSQRY.DPCode,"None"))

It would automatically place the count in that category.