Programming

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to write a program that will populate a three character field with
all possible combinations of alpha numeric characters.
ex:
"000"
"001"
"999"
etc.
"00A"
"00Z"
"ZZZ"
etc.
 
Dear MD:

Do you want 36 characters, 0-9 then A-Z? Do you want lower case as well
(could cause problems!). Do you allow punctuation?

Using only the set of 36, use modulo and integer division to retrieve 3
values 0 to 35. Add ASCII value 48 if it is less than 10 to get characters
0-9, converting that number to a character. For values 10-35, add 55 and
convert to character. Do this 3 times for 3 digits.

Here are some proposed functions to do this for you:

Public Function Test1(X As Long) As String
Test1 = Chr(X Mod 36 + IIf(X Mod 36 < 10, 48, 55))
X = X \ 36
Test1 = Chr(X Mod 36 + IIf(X Mod 36 < 10, 48, 55)) & Test1
X = X \ 36
Test1 = Chr(X Mod 36 + IIf(X Mod 36 < 10, 48, 55)) & Test1
End Function

Public Function Test2(X As String) As Long
Test2 = Asc(Right(X, 1)) - IIf(Right(X, 1) < "A", 48, 55)
Test2 = (Asc(Right(X, 2)) - IIf(Right(X, 2) < "A", 48, 55)) * 36 + Test2
Test2 = (Asc(Right(X, 3)) - IIf(X < "A", 48, 55)) * 36 * 36 + Test2
End Function

Use Test1 to create a string from any integer. Use Test2 to convert it
back.

Tom Ellison
 
for x = 48 to 57
for y = 48 to 57
for z = 48 to 57
print chr$(x)+chr$(y)+chr$(z)
next
next
next
 
If this is a school project, I suggest you try it on your own first, but the
following will do it for all combinations of digits, uppercase, and lowercase
letters. As this is about 240,000 combinations, it will take some time. If
you don't need the lower case letters, change the dimension of strAscTable to
36, remove the third For..Next loop (36 to 61), and change the counters of
the nested loops to 0 to 35.

Dim strWS As String
Dim strAscTable(62) As String
Dim inti As Integer
Dim intj As Integer
Dim intk As Integer
Dim strSQL As String

' Read ASCII characters into array

' Digits
For inti = 0 To 9
strAscTable(inti) = Chr(inti + 48)
Next inti

' Uppercase letters
For inti = 10 To 35
strAscTable(inti) = Chr(inti + 55)
Next inti

' Lowercase letters
For inti = 36 To 61
strAscTable(inti) = Chr(inti + 61)
Next inti

For inti = 0 To 61
Debug.Print inti, strAscTable(inti)
Next inti
Exit Sub

' Nested loops to create each combination
DoCmd.SetWarnings False
For inti = 0 To 61
For intj = 0 To 61
For intk = 0 To 61
strWS = strAscTable(inti) & strAscTable(intj) &
strAscTable(intk)
strSQL = "INSERT INTO [YourTable] (YourField) " & _
" SELECT " & "'" & strWS & "'" & " AS Expr1;"
DoCmd.RunSQL strSQL
Next intk
Next intj
Next inti
DoCmd.SetWarnings True
End Sub

Sprinks
 
mdais,

The For Next loop about Debug.Print is not necessary. I used to to get the
code right and forgot to delete it.

Sprinks

Sprinks said:
If this is a school project, I suggest you try it on your own first, but the
following will do it for all combinations of digits, uppercase, and lowercase
letters. As this is about 240,000 combinations, it will take some time. If
you don't need the lower case letters, change the dimension of strAscTable to
36, remove the third For..Next loop (36 to 61), and change the counters of
the nested loops to 0 to 35.

Dim strWS As String
Dim strAscTable(62) As String
Dim inti As Integer
Dim intj As Integer
Dim intk As Integer
Dim strSQL As String

' Read ASCII characters into array

' Digits
For inti = 0 To 9
strAscTable(inti) = Chr(inti + 48)
Next inti

' Uppercase letters
For inti = 10 To 35
strAscTable(inti) = Chr(inti + 55)
Next inti

' Lowercase letters
For inti = 36 To 61
strAscTable(inti) = Chr(inti + 61)
Next inti

For inti = 0 To 61
Debug.Print inti, strAscTable(inti)
Next inti
Exit Sub

' Nested loops to create each combination
DoCmd.SetWarnings False
For inti = 0 To 61
For intj = 0 To 61
For intk = 0 To 61
strWS = strAscTable(inti) & strAscTable(intj) &
strAscTable(intk)
strSQL = "INSERT INTO [YourTable] (YourField) " & _
" SELECT " & "'" & strWS & "'" & " AS Expr1;"
DoCmd.RunSQL strSQL
Next intk
Next intj
Next inti
DoCmd.SetWarnings True
End Sub

Sprinks

mdaisl said:
I need to write a program that will populate a three character field with
all possible combinations of alpha numeric characters.
ex:
"000"
"001"
"999"
etc.
"00A"
"00Z"
"ZZZ"
etc.
Click to expand...
Click to expand...
 
Sprinks, this worked great, thank you so much.
couldn't get the sql statment to work but i used a recordset
to insert the text. It created 46,656 combinations without the
lower case characters. i had attempted this using variable but,
it was getting too complicated. Your use of array to hold the ASC
code solved that problem. thanks, md

Sprinks said:
If this is a school project, I suggest you try it on your own first, but the
following will do it for all combinations of digits, uppercase, and lowercase
letters. As this is about 240,000 combinations, it will take some time. If
you don't need the lower case letters, change the dimension of strAscTable to
36, remove the third For..Next loop (36 to 61), and change the counters of
the nested loops to 0 to 35.

Dim strWS As String
Dim strAscTable(62) As String
Dim inti As Integer
Dim intj As Integer
Dim intk As Integer
Dim strSQL As String

' Read ASCII characters into array

' Digits
For inti = 0 To 9
strAscTable(inti) = Chr(inti + 48)
Next inti

' Uppercase letters
For inti = 10 To 35
strAscTable(inti) = Chr(inti + 55)
Next inti

' Lowercase letters
For inti = 36 To 61
strAscTable(inti) = Chr(inti + 61)
Next inti

For inti = 0 To 61
Debug.Print inti, strAscTable(inti)
Next inti
Exit Sub

' Nested loops to create each combination
DoCmd.SetWarnings False
For inti = 0 To 61
For intj = 0 To 61
For intk = 0 To 61
strWS = strAscTable(inti) & strAscTable(intj) &
strAscTable(intk)
strSQL = "INSERT INTO [YourTable] (YourField) " & _
" SELECT " & "'" & strWS & "'" & " AS Expr1;"
DoCmd.RunSQL strSQL
Next intk
Next intj
Next inti
DoCmd.SetWarnings True
End Sub

Sprinks

mdaisl said:
I need to write a program that will populate a three character field with
all possible combinations of alpha numeric characters.
ex:
"000"
"001"
"999"
etc.
"00A"
"00Z"
"ZZZ"
etc.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with expression 7
Inserting default value in part of input mask 3
Auto populate fields 5
Removing non Alpha Numeric Characters 6
Validation Rule Modification 7
Importing Excel Spreadsheet into Access 2000 4
truncate characters 3
NI numbers and validation 8

Back
Top