generate all possible combinations of these chars 3

[overdraft015]

i need to be able to generate a list of all the possible combinations using the following:

0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ

The lengh must be 4 long and can be made using any combination of the above

e.g. 0000, 0001, 000A, A03J, AAAA, AAA2, ABCD, 1ABC, A1BC, AB1C, ABC1 etc...

i was starting something using loops and looking up a table with the above and doing an rs.next for the next value but then i ran into a problem when trying to alter the 2nd char.


this is what i was playing with but im sure i could be over complicating things maybe. (also the table1 was just a list of the values i mentioned and a sorting order). feel free to ignore what i did as this could be the totally wrong way of starting. thanks

Private Sub calculate()

Dim myDb As Database
Dim myRs1 As Recordset
Dim StrQueryname1 As String
Dim i As Integer
dim b as

Set myDb = CurrentDb

StrQueryname1 = "SELECT Sort, Value FROM Table1 ORDER BY Sort"

c1 = 0
c2 = 0
c3 = 0
c4 = 0

Next
End

Do While c1 & c2 & c3 & c4 <> "ZZZZ"

Debug.Print "F" & c1 & c2 & c3 & c4

Do While c4 <> "Z"

c4 = myRs1!Value

Debug.Print "F" & c1 & c2 & c3 & c4
myRs1.MoveNext

Loop
Loop

End Sub



It's not what you know. It's who's on Tek-Tip's

 

[dhookom]

That's a lot (1679616 or 36^4) of records in the result. You can probably use a simple cartesian query.

Assuming your table name is OverDraft015, try something like:

SELECT OverDraft015.Value, OverDraft015_1.Value, OverDraft015_2.Value, OverDraft015_3.Value
FROM OverDraft015, OverDraft015 AS OverDraft015_1, OverDraft015 AS OverDraft015_2, OverDraft015 AS OverDraft015_3;

Duane
Hook'D on Access
MS Access MVP

 

[Blorf]

In my example I have a table called Combo, with one field, called Combo. I am running for next loops and adding the values to the table. 0 - Z is 36, and 36 ^ 4 gives you 1679616 records.

Function RunNumbers()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim X As String
Dim Y As String
X = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Set Sele1 = CurrentDb.OpenRecordset("Combo")

For A = 1 To 36
For B = 1 To 36
For C = 1 To 36
For D = 1 To 36
Y = Mid(X, A, 1)
Y = Y & Mid(X, B, 1)
Y = Y & Mid(X, C, 1)
Y = Y & Mid(X, D, 1)
Sele1.AddNew
Sele1.combo = Y
Sele1.Update
Next
Next
Next
Next
End Function


There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[Blorf]

Excellent Dhookom. Beats my code to death.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/

 

[overdraft015]

Thank you both for your help. absolutely perfect.


It's not what you know. It's who's on Tek-Tip's

 

[dhookom]

It's hard to beat pure SQL with any kind of looping code. Gotta love them Cartesians.

Duane
Hook'D on Access
MS Access MVP