Partition Function (1 Viewer)

[fenhow]

Hi, I am using a partition function as follows:
Aging: Partition(Now()-[DueDate],30,120,30)

On my report from this query there are headers 0-30, 30-59,60-89 and 90+

What is happening if my data does not have any values in the 90+ range the field on my report 90:119 throws an error saying "No Such Field in list"

I would like the report to show 90+ to be null or have a zero however when the query runs the Partition(Now()-[DueDate],30,120,30) and there are no 90+ my report will not run.

It appears that when the query is run using the partition function it only creates headers for the range that is in the data.

Is there a way to force the 90-119 into the query no matter what so my report can run? I am afraid if I remove the field from the report and a 90+ shows up it will not be reflected.

Many thanks.
Fen

 

[jdraw]

I don't think so -not with Partition, but maybe some fancy code....

see https://support.office.com/en-us/article/Partition-Function-1a846a33-60c7-4371-8e77-c94278274dc5

 

[fenhow]

Thanks, it is a large database and would be quite complex to thin it out and submit it. I basically need to know if using this code:

Aging: Partition(Now()-[DueDate],30,120,30)

I can force the query to create columns for all ranges on the 30 increment regardless of whether there are any accounts past 90 days or not.

I was thinking along the lines of

Aging: Partition(Nz(Now()-[DueDate],30,120,30),0)

but that throws an error..
Fen