Crosstab Query Question (1 Viewer)

chergh · May 22, 2007

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 · May 22, 2007

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 · May 22, 2007

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

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

.

chergh · May 22, 2007

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 · May 22, 2007

Run a make table on the crosstab and export that

chergh · May 22, 2007

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

accessuser1001 · Oct 10, 2010

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

Crosstab Query Question (1 Viewer)

chergh

blah

chergh

blah

Jon K

Registered User.

chergh

blah

Road_Warrior

Registered User.

chergh

blah

accessuser1001

New member

Similar threads

Users who are viewing this thread