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.....