Specify Null Values in a Crosstab Query?

[Jeff]

I am trying to use a crosstab query to count three textual values. In
this instance, I am trying to have it count "WINS," "LOSSES," and
"TIES" for an athletic team by opponent and then, in a separate
crosstab query, by season.

I notice that the crosstab query returns a null value for a particular
result if that result is not present. For example, if the database
shows no ties against [TeamA], the crosstab query will return a null
value for ties against [TeamA]. Wins and losses will be counted for
[TeamA], if present, as expected. I would rather the query show a
value of zero (0) if there are no instances of a tie. I vaguely
understand the concept of the Nz function, but can't seem to figure
out how to apply this to the crosstab query. Any thoughts? Thanks in
advance!

 

[Cameo]

As far as I know, crosstabs don't allow NZ functions. You will need to code
the NZ into a Select Query that references the crosstab query or into a
control on a Report.

I do this for a softball website I maintain, and I code the NZs into a
query: nz([Wins])&"-"&nz([Losses])&"-"&nz([Ties]) would look like 5-2-0.
....and then reference the query's calculated ("Record") field in the Report,
because I have multiple reports that reference a Team's record.

The W-L-T-FF records on the following pages are derived from Select Queries
that are based on Crosstab queries that calculate WLTs (FFs are forfeits).
Individual Team Schedules:
http://www.bostonwestcoedsoftball.com/BWCS-2003-Fall/html/r-1-Team.html
Division Rankings:
http://www.bostonwestcoedsoftball.com/BWCS-2003-Fall/html/r-3-Rank.html

Cameo

 

[Phobos]

Use:

newfieldname: IIf(IsNull([fieldname]),0,[fieldname])

in your crosstab query.

P

 

[Duane Hookom]

Use a value of something like:

Field: TheValue:Val(Sum(Nz([FieldToSum], 0)))
Crosstab: Value
Total: Expression

 

[Rick Brandt]

As far as I know, crosstabs don't allow NZ functions.

Sure they do. I use Nz() in most all of my Crosstabs.

 

[Cameo]

Then I must be doing something wrong, because I get error messages everytime
I do it...

I don't do crosstabs with VBA but in the Query Design view. Once you set the
crosstab, you must have a Row Heading, a Column Heading, and a Value. When I
add the expression Newfield: IIF(isnull([ValueField],0,[ValueField]) as a
new field and set it as Expression and I preview the Query, the Newfield is
not visible.

Where are you coding the NZ? I would love to know this because I had a heck
of a time working around it. If I can code the NZ directly in the crosstab,
it would save me SOOOO much work! Please tell me...I would be much indebted!
TIA...

Cameo

 

[Rick Brandt]

Then I must be doing something wrong, because I get error messages everytime
I do it...

I don't do crosstabs with VBA but in the Query Design view. Once you set the
crosstab, you must have a Row Heading, a Column Heading, and a Value. When I
add the expression Newfield: IIF(isnull([ValueField],0,[ValueField]) as a
new field and set it as Expression and I preview the Query, the Newfield is
not visible.

Where are you coding the NZ? I would love to know this because I had a heck
of a time working around it. If I can code the NZ directly in the crosstab,
it would save me SOOOO much work! Please tell me...I would be much indebted!
TIA...

Cameo

Sure they do. I use Nz() in most all of my Crosstabs.

 

[Rick Brandt]

Then I must be doing something wrong, because I get error messages everytime
I do it...

I don't do crosstabs with VBA but in the Query Design view. Once you set the
crosstab, you must have a Row Heading, a Column Heading, and a Value. When I
add the expression Newfield: IIF(isnull([ValueField],0,[ValueField]) as a
new field and set it as Expression and I preview the Query, the Newfield is
not visible.

Where are you coding the NZ? I would love to know this because I had a heck
of a time working around it. If I can code the NZ directly in the crosstab,
it would save me SOOOO much work! Please tell me...I would be much indebted!
TIA...

Not in a new field. In the field that is used for "The Value". Change the
Total setting to "Expression" and move the aggregate function to the field
definition and then wrap Nz() around it.

 

[Cameo]

I got the expression to be visible, but can't figure out the right sequence
for NZ. Where should it be? I've tried the following three variations and am
expecting a ZERO be visible where there was a null value, however, it
remains null when I use any of the formulae below. What am I doing wrong? So
close and yet so far...

nz(Count(IIf(IsNull([ScoreID]),0,[ScoreID])))

nz(Count([ScoreID]))

Count(nz([ScoreID]))

Thanks for all your help!

Cameo

Then I must be doing something wrong, because I get error messages everytime
I do it...

I don't do crosstabs with VBA but in the Query Design view. Once you set the
crosstab, you must have a Row Heading, a Column Heading, and a Value. When I
add the expression Newfield: IIF(isnull([ValueField],0,[ValueField]) as a
new field and set it as Expression and I preview the Query, the Newfield is
not visible.

Where are you coding the NZ? I would love to know this because I had a heck
of a time working around it. If I can code the NZ directly in the crosstab,
it would save me SOOOO much work! Please tell me...I would be much indebted!
TIA...

Not in a new field. In the field that is used for "The Value". Change the
Total setting to "Expression" and move the aggregate function to the field
definition and then wrap Nz() around it.

 

[Rick Brandt]

I just tested and the second one worked for me.

I got the expression to be visible, but can't figure out the right sequence
for NZ. Where should it be? I've tried the following three variations and am
expecting a ZERO be visible where there was a null value, however, it
remains null when I use any of the formulae below. What am I doing wrong? So
close and yet so far...

nz(Count(IIf(IsNull([ScoreID]),0,[ScoreID])))

nz(Count([ScoreID]))

Count(nz([ScoreID]))

Thanks for all your help!

Cameo

Then I must be doing something wrong, because I get error messages everytime
I do it...

I don't do crosstabs with VBA but in the Query Design view. Once you set the
crosstab, you must have a Row Heading, a Column Heading, and a Value. When I
add the expression Newfield: IIF(isnull([ValueField],0,[ValueField]) as a
new field and set it as Expression and I preview the Query, the Newfield is
not visible.

Where are you coding the NZ? I would love to know this because I had a heck
of a time working around it. If I can code the NZ directly in the crosstab,
it would save me SOOOO much work! Please tell me...I would be much indebted!
TIA...

Not in a new field. In the field that is used for "The Value". Change the
Total setting to "Expression" and move the aggregate function to the field
definition and then wrap Nz() around it.

 

[Cameo]

Hmm...This is quite strange. I tried the 2nd one again and it *didn't* work
for me.

I'm using Access 2000 on XP OS. For kicks, I made a copy of the DB and
turned on the Access XP functions. Neither worked.

I guess I'll have to live with it.

Thanks for your help, though. Although, I must say, it's quite frustrating
for me...

Cameo

I just tested and the second one worked for me.

I got the expression to be visible, but can't figure out the right sequence
for NZ. Where should it be? I've tried the following three variations and am
expecting a ZERO be visible where there was a null value, however, it
remains null when I use any of the formulae below. What am I doing wrong? So
close and yet so far...

nz(Count(IIf(IsNull([ScoreID]),0,[ScoreID])))

nz(Count([ScoreID]))

Count(nz([ScoreID]))

Thanks for all your help!

Cameo

Then I must be doing something wrong, because I get error messages everytime
I do it...

I don't do crosstabs with VBA but in the Query Design view. Once

you
set the

crosstab, you must have a Row Heading, a Column Heading, and a

Value.
When I

add the expression Newfield:

IIF(isnull([ValueField],0,[ValueField])
as a

new field and set it as Expression and I preview the Query, the Newfield is
not visible.

Where are you coding the NZ? I would love to know this because I

had a
heck

of a time working around it. If I can code the NZ directly in the crosstab,
it would save me SOOOO much work! Please tell me...I would be much indebted!
TIA...

Not in a new field. In the field that is used for "The Value".

Change
the

Total setting to "Expression" and move the aggregate function to the field
definition and then wrap Nz() around it.

 

[Jeff]

Thanks to everyone for their responses. I was able to get the
database to do what I need it to do using the indirect method
recommended here. I'll try the other methods later. Thanks again!