Blank Values in Crosstab Query 1

[EliseFreedman]

I have a crosstab query which is based on a normal select query. It calculates the amount of stock returned by all our customers. The crosstab query displays the amount of stock returned each month for each customer

i.e

Jan Feb Mar Apr.........

Focus DIY 9.40 6.2
Stax 8.5 5.4

Basically, where they have no returns for a particular month, I would like to insert a zero so that I end up with the following



Jan Feb Mar Apr.........

Focus DIY 9.40 0 6.2 0
Stax 0 8.5 0 5.4

How would I do this. I have tried changing the value in the crosstab query to IIf(IsNull([Total]),0,[Total])) but it didnt make any difference.

 

[zrzr]

Try with

Nz([Total],0)

Or if you really want to use iif (but Nz is specially do to perform this kind of job) :

IIf(IsNull([Total])=True,0,[Total])

 

[EliseFreedman]

Thanks zrzr, that worked great and I now have zeros instead of blanks. However, now all the values are no longer rounded to two dp. Originally, when I highlighted the Value column and went into properties, I had the option to format the value i.e couple make it 2 decimal places. This is no longer available and I now get values like
79.1999988555908
How do I round it to two decimal places??

 

[EliseFreedman]

It's OK zrzr. I found a post on google asking the same question. When I put CDbl around my NZ expression
i.e CDbl(NZ(Sum([GoodStockReturns].[Total]),0)), it worked like a dream.

Thanks for your help

 

[Keetso]

Hi.

I'm having the same problem with my crosstab query which is based on an existing query (using two underlying tables: tblcompany, tblmonthlydata). This query (the underlying one, not the crosstab)takes two types of commissions (2 separate fields) and sorts and sums each of them according to broker name, city and company. See simplified sample output below:

BrokerName CompName CommA CommB City
Joe Smith CompA $20 $40 Miami
Joe Smith CompA $10 $0 Miami
Mary Jane CompA $10 $30 Chicago
Joe Smith CompB $10 $20 Chicago
Cindy Jones CompC $10 $0 NYC

The crosstab uses the office location (Miami, Chicago, NYC and LA) as the row, company name as the column and the summed commissions as the "value" field to give company totals for each city. See sample output below for "CommA"("CommB" would have a similar output):

CompA CompB CompC CompD
Miami $30
Chicago $10 $40
NYC $10
LA $25 $30 $30

As you can see, where there's been no activity for a given Company in a given city (ex Miami/CompC), the output is blank.
I tried both ways listed in this thread (using "Nz" AND "IIF" methods to place zeroes when the "value" field is Null. I'm getting no error messages but the null fields are still showing up as blank. No changes.

This query is being used as the recordsource for a report so I need all the value fields to print with the Null (blank) fields showing as zero. I understand why the fields are showing blank (Null) as the querie's values are generated from records in tblMonthlyData and if there's no activity for a company/city then theres no corresponding record(s). This is where the NZ function is supposed to come in handy, correct?

Any ideas as to what I'm doing wrong and why it's not working for me?

TIA

K

 

[dhookom]

It might help to see your SQL view. Otherwise, you can set the format property of the text box in the report to display $0 rather than blank. The format for numeric text boxes has 4 parts. The 1st for positive, 2nd negative, 3rd for zero, and 4th for null. From Help:

$#,##0.00[Green];($#,##0.00)[Red];"Zero";0

Duane
MS Access MVP
Find out how to get great answers faq219-2884.