# Numeric Result for Combinations

1. Virtually Inert Person Windows 10 Access 2016            Join Date
Jun 2014
Location
Posts
6,303

## Numeric Result for Combinations

I wanted to replicate Excel's Combin function just for the heck of it but tried to see if it was something that's been done a thousand times already. All I could find was how to make all the possible combinations from table data using sql tricks, which is fine if you have the data and want to pair it up. If all you want is a number, that seems to be lacking for Access, which is probably because everyone just flips over to Excel for a moment. Anyway, here's a little routine which solved the most pressing problem at the moment - not being able to get to sleep!

Code:
```Function nCr(ByVal n As Long, ByVal r As Long) As String
'nCr = n! / r! * (n-r)!
Dim cnt As Integer
Dim dblN As Double, dblR As Double, dblNR As Double

'get n!
dblN = 1
For cnt = 1 To n
dblN = dblN * cnt
Next

'get r!
dblR = 1
For cnt = 1 To r
dblR = dblR * cnt
Next

'get (n-r)!
dblNR = 1
For cnt = 1 To (n - r)
dblNR = dblNR * cnt
Next

nCr = Format(dblN / (dblR * dblNR), "#,###")

End Function```
The max number you can use for n or r is 170, otherwise you get overflow. What's really odd is that for n = 170, r = 80 or r = 90 produces the same result.
Verified in Excel!

Maybe it will help someone some day.....   Reply With Quote

2. VIP Windows XP Access 2003        Join Date
Jul 2011
Posts
5,440
Interesting problem, but access hates large numbers could you compress the code a bit though:

Code:
```Dim X, Y, Z as double
Dim C As double
Dim XFact, YFact, ZFact

X = 150
Y = 2
Z = X - Y
C = 1

For i = 1 To X
C = C * i
If CLng(i) = CLng(X) Then XFact = C
If CLng(i) = CLng(Y) Then YFact = C
If CLng(i) = CLng(Z) Then ZFact = C
Next i

Result = XFact / (YFact * ZFact)
debug.print result```  Reply With Quote

3. Virtually Inert Person Windows 10 Access 2016            Join Date
Jun 2014
Location
Posts
6,303
nice and simplified approach, but is some of your code missing from your post - or you don't require variable declaration maybe?
There's no declaration for i or for result. I presume you do know that X and Y are variants and not doubles. Is it safer/better to have them as variants for your method?  Reply With Quote

4. VIP Windows XP Access 2003        Join Date
Jul 2011
Posts
5,440
I actually chopped it out of a form and had my fields labeled x and y, I just threw in the variable declarations to post here, I just thought it easier to loop through one time as opposed to 3 times. Like you said though, I can't find an easy way to get a factorial over 170.  Reply With Quote

5. Virtually Inert Person Windows 10 Access 2016            Join Date
Jun 2014
Location  Reply With Quote