Blank fields - how to change to 0

[AccessDeano]

Hi, I am a newbie so bear with me.

I have a table which has data

record1 | question 1 | 3
record1 | question2 | 2
etc

I have a cross tab query that generates a table that now looks like

Headings
Question | 1 | 2 | 3 | 4|

and the data shows how many questions were answered as 1, 2 etc.

However when I generate a report I get several fields that are blank because there were no answers for that value with that specific question.

How to I change the box in the report to 0 when the field is blank?

I hope the above makes sense.

 

[llkhoutx]

Use the NZ function, i.e. nz(YourField)

 

[AccessDeano]

Forgive me for being a little? (maybe alot) dense.

Where to I put the NZ function?

I am a vey new newbie!

 

[llkhoutx]

In fie field line of your crosstab query

 

[AccessDeano]

I appreciate your help so far with this

Unfortunately, that did not work

My query is

TRANSFORM Count(NewResponses.Response) AS CountOfResponse
SELECT NewResponses.QuestionId, NewResponses.SortData
FROM NewResponses
WHERE (((NewResponses.Date) Between [Forms]![datetest]![StartDate] And [Forms]![datetest]![EndDate]))
GROUP BY NewResponses.QuestionId, NewResponses.SortData
ORDER BY NewResponses.SortData
PIVOT NewResponses.Response In (1,2,3,4);

When I changed it to

TRANSFORM Count(NZ([Response])) AS Expr1
SELECT NewResponses.QuestionId, NewResponses.SortData
FROM NewResponses
WHERE (((NewResponses.Date) Between [Forms]![datetest]![StartDate] And [Forms]![datetest]![EndDate]))
GROUP BY NewResponses.QuestionId, NewResponses.SortData
ORDER BY NewResponses.SortData
PIVOT NewResponses.Response In (1,2,3,4);

the results are exactly the same. I have tried using the other "Responses" field but that didn't work either.

Am I doing something wrong?

 

[llkhoutx]

Can you post your table and crosstab query?

 

[AccessDeano]

I am not quite sure how to add certain tables, etc.

I have attached the mdb which is Access 97 format.

Kind regards

 

[llkhoutx]

I need to see your relevant table(s) and query, otherwise I have to build them which I don't have the patience or time to do.

The mdb you posted is of no help in this regard.

 

[AccessDeano]

I am sorry, I am confused.

The mdb is all that I have.

If you open the report and enter dates from 01-01-01 to 04-04-04 as an example the report is generated with several blank fields.

I do not have any other tables, etc.

I appreciate your efforts and happily accept that I am probably such a newbie. I also appreciate that you may not have the time to look at this and have no problems if you do not respond.

Thanks for trying to help.

Kind regards

 

[llkhoutx]

There is no report in the mdb. No data either, only tables New Service and Used. All empty. Form New has two radio buttons, bound to a query which does not exist.

 

[AccessDeano]

I am very sorry, I did not engage brain before adding the attachment. It was an earlier attempt which I should have deleted.

The correct file can be downloaded from http://www.auto-alert.net/access/csi.zip as it is too large to upload here (file size is 250Kb).

This contains all of the information.

Many thanks

 

[Rich]

Expr1: Nz(Count([Response]),0)

 

[AccessDeano]

Thank you very much.

I had just found another post using NZ and had tried to get it to work, but with the expression the other way around - Count(nz([],)) and not suprisingly it didn't work.

Thanks again.