Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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 arguments:

Argument

Description

number

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

start

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

stop

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

interval

Required. Whole number that specifies the size of the partitions within the overall range of numbers (between start and stop).

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, stop, and interval arguments. The First Range and Last Range columns show what 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 (:).

start

stop

interval

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 stop define a set of numbers that can't be evenly divided by interval. The last range extends to stop (11 numbers) even though interval is 20.

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 number:number, regardless of the start and stop arguments. For example, if interval is 1, number is 100 and stop is 1000, Partition returns " 100: 100".

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

Query examples

Expression

Results

SELECT Partition(unitprice,40,240,20) AS PriceRange, count(productsales.unitprice) AS Count FROM productSales GROUP BY Partition(unitprice,40,240,20);

Creates a set of ranges for the values in the field "unitprice" from "start"(40) to "stop"(240) with equal "interval"(20) size and calculate the count of "unitprice" in respective ranges. Displays the ranges in column PriceRange and count in column 'Count'.

VBA example

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);

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×