Null value in Crosstab Query

S

skwilliams

Registered User.

Local time

Today, 10:46

Joined

Jan 18, 2002

Messages

516

• Mar 25, 2004

• #1

I have a viewable form whose record source is a crosstab query. I have a couple unbound fields which total specific fields on the form. It works fine.

My problem is certain fields may be null, if this happens the unbound fields won't total.

Is it possible to tell it that if I field is null to insert a zero or not figure the field in the calculation at all?

Thanks.

 

o1110010

Registered User.

Local time

Today, 09:46

Joined

Mar 6, 2004

Messages

182

• Mar 25, 2004

• #2

I think I would fix this issue by setting a default value of 0 (zero) to fields. Then the fields should never be null.

skwilliams said:

Is it possible to tell it that if I field is null to insert a zero or not figure the field in the calculation at all?

Click to expand...

Those I do not know. Sorry.

 

S

skwilliams

Registered User.

Local time

Today, 10:46

Joined

Jan 18, 2002

Messages

516

• Mar 25, 2004

• #3

I set the default value to zero but it still shows null

 

o1110010

Registered User.

Local time

Today, 09:46

Joined

Mar 6, 2004

Messages

182

• Mar 25, 2004

• #4

skwilliams said:

I set the default value to zero but it still shows null

Click to expand...

I mean setting a default value to the objects where the crosstab query gets it's data. Sorry.

 

S

skwilliams

Registered User.

Local time

Today, 10:46

Joined

Jan 18, 2002

Messages

516

• Mar 25, 2004

• #5

The crosstab query gets it's data from the table [tblAccum]. I set the default value in that table and also on the form itself.

 

o1110010

Registered User.

Local time

Today, 09:46

Joined

Mar 6, 2004

Messages

182

• Mar 25, 2004

• #6

skwilliams said:

The crosstab query gets it's data from the table [tblAccum]. I set the default value in that table and also on the form itself.

Click to expand...

Hm. How is the crosstab query getting NULL values if all values in tblAccum have atleast a default value? What calculations is the crosstab doing?

 

S

skwilliams

Registered User.

Local time

Today, 10:46

Joined

Jan 18, 2002

Messages

516

• Mar 25, 2004

• #7

I've attached a zipped word document showing sample data and what is being displayed in the crosstab query.

If a date doesn't contain a certain prodcode in the table, the crosstab query interprets it as a null value in the query.

Does this make sense?

 

Attachments

• tblAccum.zip

  36.4 KB · Views: 688

R

Rich

Guest

• Mar 25, 2004

• #8

look up the Nz function

 

S

skwilliams

Registered User.

Local time

Today, 10:46

Joined

Jan 18, 2002

Messages

516

• Mar 26, 2004

• #9

I am using Nz in the SQL statement for the crosstab query. It doesn't seem to be having favorable results though.

Am I using it properly?

Here's the SQL statement.

TRANSFORM Sum(Nz([tblAccum].[COUNT])) AS SumOfCOUNT
SELECT [tblAccum].[Type], [tblAccum].[RecDate], Sum([tblAccum].[COUNT]) AS TOTAL
FROM tblAccum
GROUP BY [tblAccum].[Type], [tblAccum].[RecDate]
PIVOT [tblAccum].[ProdCode];

 

R

Rich

Guest

• Mar 26, 2004

• #10

have you tried TRANSFORM Sum(Nz([tblAccum].[COUNT],0))

 

S

skwilliams

Registered User.

Local time

Today, 10:46

Joined

Jan 18, 2002

Messages

516

• Mar 26, 2004

• #11

Yes, I did.

I did resolve my problem though. I used the Nz in the unbound fields of the form and it's working properly now.

Thanks.

 

J

Jon K

Registered User.

Local time

Today, 15:46

Joined

May 22, 2002

Messages

2,209

• Mar 27, 2004

• #12

have you tried TRANSFORM Sum(Nz([tblAccum].[COUNT],0))

Yes, I did.

Click to expand...

In a crosstab query, it is:-

TRANSFORM Nz(Sum(tblAccum.[COUNT]))+0

 

R

RobertL

Guest

• Apr 1, 2004

• #13

Jon K said:

In a crosstab query, it is:-

TRANSFORM Nz(Sum(tblAccum.[COUNT]))+0

Click to expand...

Wow, I was having the same problem and this fixed it, thanks a ton.