Results 1 to 5 of 5
  1. #1
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702

    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.....
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    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?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    I would think it's not possible as the result is a number that's just too large for Access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-25-2018, 04:47 AM
  2. Finding all possible combinations
    By fauowls in forum Access
    Replies: 3
    Last Post: 03-23-2013, 05:34 PM
  3. How to search for duplicate combinations
    By EvanRosenlieb in forum Access
    Replies: 5
    Last Post: 04-17-2012, 02:56 PM
  4. Calculating all possible combinations
    By Data in forum Queries
    Replies: 3
    Last Post: 06-20-2011, 03:02 PM
  5. Query for All Possible Combinations
    By Rawb in forum Queries
    Replies: 1
    Last Post: 10-09-2010, 08:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums