G
Guest
I want to find all combinations of the following amounts in 'Table1'.
Ultimately, I want the database to report all combinations that are within 5
of a user-specific target (forms!form1!target). The current target I am
trying is 6145.45; so I want to report any combinations that are within
6140.45 to 6150.45.
--------------------------
Record Amount
1 570.02
2 71.26
3 210.19
4 947.64
5 979.7
6 944.09
7 948.83
8 947.63
9 142.52
10 60.57
11 748.14
12 523.69
--------------------------
The following code, which is activated with a button in form1, works, but it
doesn't look at every combination since it only counts sequentially. For
example, it doesn't consider the combination of amounts in records 3, 6, and
10. I don't have to do this in VBA if SQL is easier. Any thoughts?
--------------------------
Private Sub Command0_Click()
Dim x As Single
Dim recordmax As Long
Dim i, j, k As Long
i = 0
j = 0
k = 0
x = 0
recordmax = DMax("record", "table1")
Do While i < recordmax
j = 0
i = i + 1
x = DLookup("amount", "table1", "record = " & i)
Do While j < recordmax
j = j + 1
If j = i Then GoTo skip: 'don't include the j
record twice
x = x + DLookup("amount", "table1", "record = " & j)
'temp output to screen; will eventually write results to a
table
MsgBox i & " : " & j & " : " & x
skip:
Loop
Loop
End Sub
Ultimately, I want the database to report all combinations that are within 5
of a user-specific target (forms!form1!target). The current target I am
trying is 6145.45; so I want to report any combinations that are within
6140.45 to 6150.45.
--------------------------
Record Amount
1 570.02
2 71.26
3 210.19
4 947.64
5 979.7
6 944.09
7 948.83
8 947.63
9 142.52
10 60.57
11 748.14
12 523.69
--------------------------
The following code, which is activated with a button in form1, works, but it
doesn't look at every combination since it only counts sequentially. For
example, it doesn't consider the combination of amounts in records 3, 6, and
10. I don't have to do this in VBA if SQL is easier. Any thoughts?
--------------------------
Private Sub Command0_Click()
Dim x As Single
Dim recordmax As Long
Dim i, j, k As Long
i = 0
j = 0
k = 0
x = 0
recordmax = DMax("record", "table1")
Do While i < recordmax
j = 0
i = i + 1
x = DLookup("amount", "table1", "record = " & i)
Do While j < recordmax
j = j + 1
If j = i Then GoTo skip: 'don't include the j
record twice
x = x + DLookup("amount", "table1", "record = " & j)
'temp output to screen; will eventually write results to a
table
MsgBox i & " : " & j & " : " & x
skip:
Loop
Loop
End Sub