Crosstab Query Question

[chergh]

Is there anyway to have fields which are empty display a 0 instead of being blank when using a crosstab query?

Solved by a pointer to the nz function in another thread.

 

[chergh]

Ok nz doesnt work as it doesn't work in excel. Guess I will just need to pull over the raw data then create a pivot table in excel.

 

[Jon K]

In a crosstab query, you can put Nz() in the Transform expression like this:-

TRANSFORM Nz(.......)+0 AS ......
SELECT ..........
FROM ..........
GROUP BY .........
PIVOT .........

.

 

[chergh]

Yeah i know that, and that is what I did. The issue is that I am working in excel and using excel VBA to pull data from access into excel. When I try and pull the data accross I get the following error message in the following line of code.

Set rs = qdf.OpenRecordset

i get a "Undefined function 'nz' in expression.

As nz doesnt work in excel it's probably just going to be easier to pull the rawdata across and use pivot tables in excel to put the data into a graphable form.

 

[Road_Warrior]

Run a make table on the crosstab and export that

 

[chergh]

Nah that would pointless as I would still need to do the pivot table stuff when I put the recordset into excel.

 

[accessuser1001]

Thank you so much for this post. You have made my life immensely easier & made me look good at work too! Thanks again!