Replies

Thanks Hans That worked.

You are a champ!

And amazingly I got it to work on another similar report. I’m on a roll now.

Using the SQL below I managed to get the report to run even with null values. It would be nice to put zeros in where there is no data but it isnt life threatening. I am not sure how to incorporate your last suggestion to do this thoug?

TRANSFORM Count(tbl_Applications.ProjectID) AS CountOfProjectID
SELECT tbl_Applications.StaffName, tbl_Applications.Region, Count(tbl_Applications.ProjectID) AS [Total Of ProjectID]
FROM lup_Criteria INNER JOIN tbl_Applications ON lup_Criteria.CriteriaID = tbl_Applications.CriteriaID
WHERE (((tbl_Applications.Status)=”active”) AND ((lup_Criteria.Criteria)=”Caseload”))
GROUP BY tbl_Applications.StaffName, tbl_Applications.Region
PIVOT Nz(tbl_Applications.SupervisionStandard,”Unknown”) In (“M1″,”M2″,”M3″,”DC1″,”DC2″,”C1″,”C2″,”C3″,”R”,”I”,”Unknown”);

Hi Hans
Thanks for your reply. I have tried my best to get my head around this but I am still having trouble. When I have a Supervision Standard that has no entry. As you would say a null value I need a 0 zero returned into the column.
At the moment there is no one with an M3 code so the report wont work. But it could be any of the codes at anytime.

The supevision standards are “M1″;”M2″;”M3″;”DC1″;”DC2″;”C1″;”C2″;”C3″;”R”;”I”;””

Be patient with me Hans its been a while since I worked with crosstabs and I always did have trouble with them.

Thanks for pointing this out to me. Yes it does exactly what I want.

Thanks Hans. Once again – exactly what I was looking for.
Seeya

Thanks this is also very useful.

Regards Kerry

Thanks again Hans – this is exactly what I was looking for.

Kerry

Duh! I feel like a right twit! I tried the is null stuff but not Or. Must be too late in the day for my brain.

Thanks Hans.

Sorry to take so long to get back. Yes you were right. I have a fiddle with the paragraph in question and it worked.

Thanks Hans

Edited by HansV to remove white space from attached image

Hans I am sorry to bug you again. I successfully run this macro today at work. Now I have brought it home and run it on both my laptop and desktop and keep getting this error message.

I made a template from the actual document and then referenced the file name and path in the macro “Set docNew = Documents.Add(Template:=”F:split template.dot”)”

What am I doing wrong?

Hans you are a champ! Thankyou once again. It all works perfectly now.

This is soooo handy for me to be able to do.

Kind Regards

I have been fiddling all afternoon and was just about to send you this sample to show you what is happening. Can you take a look at it before I attempt your other solution?

Thanks

Hans it is wonderful! Thankyou.

I am having just one minor problem. My styles seem to be changing when it I do the split. Not sure why.

Just trust me Hans, I do have a need to split a large document. Can you help me with this please?

That wasnt possible as there are links between chapters and our bandwidth didnt allow such big documents. Trust me I had my reasons to go to this program.