# Partition Function

**Office 2013 and later**

Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation. |

Returns a **Variant** (**String**) indicating where a number occurs within a calculated series of ranges.

**Syntax**

**Partition( number, **

*start*,

*stop*,

*interval*)The **Partition** function syntax has these named arguments:

Part | Description |
---|---|

| Required. Whole number that you want to evaluate against the ranges. |

| Required. Whole number that is the start of the overall range of numbers. The number can't be less than 0. |

| Required. Whole number that is the end of the overall range of numbers. The number can't be equal to or less than |

**Remarks**

The **Partition** function identifies the particular range in which ** number** falls and returns a

**Variant**(

**String**) describing that range. The

**Partition**function is most useful in queries. You can create a select query that shows how many orders fall within various ranges, for example, order values from 1 to 1000, 1001 to 2000, and so on.

The following table shows how the ranges are determined using three sets of ** start**,

**, and**

*stop***parts. The First Range and Last Range columns show what**

*interval***Partition**returns. The ranges are represented by

*lowervalue*:

*uppervalue*, where the low end (

*lowervalue*) of the range is separated from the high end (

*uppervalue*) of the range with a colon (

**:**).

| | | Before First | First Range | Last Range | After Last |
---|---|---|---|---|---|---|

0 | 99 | 5 | " :-1" | " 0: 4" | " 95: 99" | " 100: " |

20 | 199 | 10 | " : 19" | " 20: 29" | " 190: 199" | " 200: " |

100 | 1010 | 20 | " : 99" | " 100: 119" | " 1000: 1010" | " 1011: " |

In the table shown above, the third line shows the result when ** start** and

**define a set of numbers that can't be evenly divided by**

*stop***. The last range extends to**

*interval***(11 numbers) even though**

*stop***is 20.**

*interval*If necessary, **Partition** returns a range with enough leading spaces so that there are the same number of characters to the left and right of the colon as there are characters in ** stop**, plus one. This ensures that if you use

**Partition**with other numbers, the resulting text will be handled properly during any subsequent sort operation.

If ** interval** is 1, the range is

**, regardless of the**

*number:number***and**

*start***arguments. For example, if**

*stop***is 1,**

*interval***is 100 and**

*number***is 1000,**

*stop***Partition**returns " 100: 100".

If any of the parts is Null, **Partition** returns a **Null**.

This example assumes you have an Orders table that contains a Freight field. It creates a select procedure that counts the number of orders for which freight cost falls into each of several ranges. The **Partition** function is used first to establish these ranges, then the SQL Count function counts the number of orders in each range. In this example, the arguments to the **Partition** function are *start* = 0, *stop* = 500, *interval* = 50. The first range would therefore be 0:49, and so on up to 500.

SELECT DISTINCTROW Partition([freight],0, 500, 50) AS Range, Count(Orders.Freight) AS Count FROM Orders GROUP BY Partition([freight],0,500,50);