Partition() to show all ranges, including those with zero records

  • Thread starter Thread starter P Fu
  • Start date Start date
P

P Fu

Hi,

Range: Partition([Age],0,100,10)

I used the above statement in one of the fields in my query. I
noticed that any bin that does not have any records to count does not
even show up in the resulting table. My question is, how can I get it
to show ALL bins, even those without any 'counts' to report?

Thanks!
 
I assume you are using this as the column header of a crosstab query.

Crosstab columns will only show if data is present unless you pre-specify
the columns you want to see. This should be done with care, because if you
spell a column header incorrectly then that column will be blank and the
real data will not be displayed.

If you display the query's Properties window, you will see a place for
Column Headings. In here, type:
" 0: 9"," 10: 19", ... ,"100: "

You will need to take care with the number of spaces so they are just the
same as in the data.

Actually, here's an idea - copy the following code and paste it into a new
module and run it (F5).

Sub MakeHeadings()
Dim i As Integer
For i = 0 To 100 Step 10
Debug.Print """"; Partition(i, 0, 99, 10); """,";
Next
Debug.Print
End Sub

Now copy the result from the Debug window (except the final comma) and paste
it into the query properties window.

Note that I used 99 instead of 100 as the upper limit. This is to avoid
getting a separate bin (100:100) for anyone lucky enough to be 100 years
old. You should change this in your query also.
 
Back
Top